Sum amounts based on dates in a column range and a project # in a specific cell

On one worksheet I have a set of Dates in Column H, Amounts in Column I, and Project #'s in Column L. I want to get the sum of the project # (in column I, GL Tab) for the first quarter of the year only (based on Column H, GL tab) on the CP tab. As time goes on I will want Quarter 2,3, & 4 in separate columns on the CP tab... I have tried several formulas, but they just aren't working out. I have done sumifs, sumif, and if formulas.

Does anyone have a suggestion? Seems like this should be easy but my spreadsheet just isn't working out. I'm going cross-eyed at this point.

Here's the last formula I tried, but it just give's me $0.00's... I thinking my date criteria is messing things up.

Please Login or Register  to view this content.

I have attached a small spreadsheet with examples. I want my formulas on the "CP" Tab, my orig data is on the GL tab.

Any help would be appreciated.

you have a couple of issues with your sheet, the first is your dates are formated as text, you will need to change those to Date values, there are a few different ways to do that, let me know if you need help with that. the second issue was that you only had one date criteria, which is ok if you want the totals of everythign up to and including that Q, but if you want just one Q you need to band it on both sides, the formula bellow should help you.

=SUMIFS(GL!$I:$I,GL!$H:$H,">=1/1/2011",GL!$H:$H,"<="&E$2,GL!$L:$L,$A6)

that works as well as any method, but if you had highlighted all of your dates, there would have been a context box asking you to convert them to date value, might have been a bit quicker, but same result.