Sum cells that meet ALL criteria

I have a column of figures in H:H that I want to sum should all criteria be met and not just any of the criteria but I cannot seem to get this to work.

I can get each criteria to work on it's own but have not worked how to combine them; can anyone help?

Please Login or Register  to view this content.

This checks that the date in D1 is after the Start Date in Column A.

Please Login or Register  to view this content.

This checks that the only figures totalled are for a particular collection date.

Please Login or Register  to view this content.

This checks that no End Date has been entered in Column B.

Please Login or Register  to view this content.

This checks that if there is an End Date entered it is before the date we are summing the data for.


I really hope one of you clever folk can suss this. TIA

You're using 2007, so you can switch from SUMIF to SUMIFS().

=SUMIFS(SumRange, FirstTestRange, FirstTest, SecondTestRange, SecondTest, ThirdTestRange, ThirdTest, FourthTestRange, FourthTest)

....etc.

But the 3rd and 4th conditions are mutually exclusive, column B can't be blank and > D1 so you probably need 2 SUMIFS, one with conditions 1,2 and 3 and one with 1,2 and 4.....then add the 2 SUMIFS together...

=SUMIFS('DD''s'!H:H,'DD''s'!A:A,"<" & D1,'DD''s'!C:C,C2,'DD''s'!B:B,"")+SUMIFS('DD''s'!H:H,'DD''s'!A:A,"<" & D1,'DD''s'!C:C,C2,'DD''s'!B:B,">"&D1)