# Thread: Need Help With 2 Excel 2003 Formulas

1. ## Need Help With 2 Excel 2003 Formulas

See attachment.

I need help with two formulas in Excel 2003--
(1) The number of holidays (numbered, in bold font and also indicated "OFF" in column H) that I have taken to date.
(2) The number of such holidays remaining in the year that I have not yet taken.

Thank you,
Robert

2. Robert,

Days Taken: =COUNTIF(\$H\$9:\$H\$29,"=OFF")

Not Taken: =COUNTIFS(\$H\$9:\$H\$29,"",\$G\$9:\$G\$29,"<>")

HTH

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

rjstorms (2014-09-03)

4. Thanks for the reply but it does not solve the issue.

Formula 1 should return 5 because it was the 5th holiday taken this year as of today's date.
Formula 1 should return 4 because it there are 4 holidays yet to be taken following today's date.

Robert

5. Correction -
Formula 2 should return 4
Robert

6. Robert,

Sorry missed the YTD part.
Spec 2 should have included: (2) The number of such holidays remaining in the year that I have not yet taken also marked as OFF.

Days Taken: =COUNTIFS(\$H\$9:\$H\$29,"=OFF",\$E\$9:\$E\$29,"<="&\$E\$31) Returns: 5

Not Taken: =COUNTIFS(\$G\$9:\$G\$29,"<>",\$H\$9:\$H\$29,"=OFF",\$E\$9:\$ E\$29,">="&\$E\$31) Returns: 4

HTH

Update: Sorry these won't work for you if you have Excel 2003 as COUNTIFS shows up in Excel 2007+. Maybe someone else can help.

7. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

rjstorms (2014-09-03)

8. Thanks again for the reply, but the issue is still not resolved.
You are correct - COUNTIFS function does not work in Excel 2003, which is my version.
Any other ideas, or can anyone else help?

Robert

9. Robert,

Here's a SumProduct Version which should work in Excel 2003:

Days Taken: =SUMPRODUCT((\$H\$9:\$H\$29="OFF")*(\$E\$9:\$E\$29<=\$E\$31) )

Not Taken: =SUMPRODUCT((\$G\$9:\$G\$29<>"")*(\$H\$9:\$H\$29="OFF")*(\$ E\$9:\$E\$29>=\$E\$31))

HTH

10. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

rjstorms (2014-09-03)

11. Thank you very much! Works like a charm!
This was driving me crazy - just a little below my skill level with context details.
Robert

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•