Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Robert,

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

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

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    rjstorms (2014-09-03)

  4. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    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. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Correction -
    Formula 2 should return 4
    Robert

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    Last edited by RetiredGeek; 2014-09-03 at 21:05.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    rjstorms (2014-09-03)

  8. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    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. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    rjstorms (2014-09-03)

  11. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts
    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
  •