Sumifs formula returns ZERO

**Xx7** · 05-29-2013, 05:38 PM

Why does this return "0"?

=SUMIFS(B:B,A:A,998001,A:A,110450)

**JieJenn** · 05-29-2013, 05:43 PM

Because not both criteria are met

Try this one (Enter with Ctrl + Shift + Enter keys)
=SUM(IF($A:$A={110450,998001},B:B))

**Pete_UK** · 05-29-2013, 05:44 PM

The conditions in SUMIFS are effectively ANDed together, for each cell in turn. So, A1 cannot be both 998001 AND 110450, and neither can A2, so it returns zero as there is nothing to add up.

Hope this helps.

Pete

**Xx7** · 05-29-2013, 09:10 PM

Originally Posted by JieJenn

Because not both criteria are met

Try this one (Enter with Ctrl + Shift + Enter keys)
=SUM(IF($A:$A={110450,998001},B:B))

Thanks, I understand the criteria now. I couldn't get your formula working.

Is there an easier way than this --> =SUMIFS(B:B,A:A,998001)+SUMIFS(B:B,A:A,110450)

**Tony Valko** · 05-29-2013, 09:16 PM

One way...

=SUM(SUMIF(A:A,{998001,110450},B:B))

**Xx7** · 05-29-2013, 09:19 PM

Nice, thanks!

I was just going to post this: =SUM(SUMIFS(B:B,A:A,{110450,998001}))

**Tony Valko** · 05-29-2013, 09:29 PM

Yeah, that will also work.

**kevvyb2003** · 06-10-2013, 12:51 PM

Originally Posted by Xx7

Nice, thanks!

I was just going to post this: =SUM(SUMIFS(B:B,A:A,{110450,998001}))

Hi just found this and it's very useful but can I add nother criteria range to that formula? Have been trying and have not yet managed it.

Just worked it out:

=SUM(SUMIFS(F17:F31,C17:C31,{"day","night"},G17:G31,{"perm"}))

**Tony Valko** · 06-10-2013, 02:05 PM

The forum owner prefers that you start your own thread rather than posting your question(s) in someone elses thread.

Rule #2 of the forum rules:

http://www.excelforum.com/forum-rule...rum-rules.html