# Sum cells that meet ALL criteria

1. ## 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

2. ## Re: Sum cells that meet ALL criteria

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

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

....etc.

3. ## Re: Sum cells that meet ALL criteria

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)

4. ## Re: Sum cells that meet ALL criteria

I have tried the following sumifs formula but it is not bringing back a zero when I should have an actual figure.

``Please Login or Register  to view this content.``
I have probably typed something wrong so hopefully you will spot my error.

5. ## Re: Sum cells that meet ALL criteria

@ DLL: I see that now yes. I am an idiot. Will try again

6. ## Re: Sum cells that meet ALL criteria

Yes, that's it!
I can't believe I was almost there but for those pesky kids........I mean, those conflicting statements.
Thank-you both for helping me out.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1