Need assistanct with sum formula based on a date as well a special text condition

[lilsnoop]

Hello, I need assistance if possible to have a formula look for any cells in D1 to GG1 that have "H" in it and if they fall pre-anniversary to a specified date then it would sum the individual cell (starting in row 3 and below as determined by the "H" in row 1) hours and sum them up. I've attached a sample workbook with probably a better explanation.

As holiday dates can change from year to year I need the formula to be flexible in that it will rely on the "H" placement ; i.e. the formula once completed would allow another "H" to be put in any spot in row 1 and any hours would be calculated either pre or post anniversary with any other data collected with a "H" designator. Hope this makes sense.

This formula helped determine my pre-anniversary hours:

Please Login or Register  to view this content.

and this formula will locate my "H" and sum the necessary info but it doesn't differentiate the anniversary dates Pre and/or Post:

Please Login or Register  to view this content.

I need a formula that will combine them. Any assistance would be greatly appreciated..if this is even possible. Thanks in advance!

[FDibbins]

you are using 2007, that includes a new function called sumifS(). it allows for a far greater range of criteria, give it a try and see if that will give you what you need?

[Moo the Dog]

Try this for PRE-anniversary [GO3]:

Please Login or Register  to view this content.

and this for POST-anniversary [GP3]:

Please Login or Register  to view this content.

- Moo

[lilsnoop]

Thanks Moo and FDibbins!! I appreciate your advice and help! I had researched the sumifs function and this formula worked for the pre:

Please Login or Register  to view this content.

and this formula for the post

Please Login or Register  to view this content.

Moo I'll play with your formula too just to make sure mine doen't run into any issues. Thanks again!!

[Moo the Dog]

You're welcome, glad to help.
- Moo

* And nice solution with the SUMIFS too.

[FDibbins]

happy to help