# Sumifs returns 0

Printable View

• 05-15-2019, 11:00 AM
belinda200
Sumifs returns 0
Hi All,
I using sumif formula in the attached excel and getting result 0 (cell A13)

when I do the same with sumif for each condition seperately it returns the correct number.

Can you please help to figure out what is wrong with the formula?

Thanks!
• 05-15-2019, 11:11 AM
modytrane
Re: Sumifs returns 0
Use this formula instead:

HTML Code:

`=SUMIF(A2:A11,A2,B2:B11)+SUMIF(A2:A11,A6,B2:B11)`
The way you wrote it, both conditions have to be met.
• 05-15-2019, 11:17 AM
Melvosh
Re: Sumifs returns 0
You could do something like this, but it would require your conditions to be in a contiguous range:
Code:

`=SUMPRODUCT(SUMIFS(B2:B11,A2:A11,A5:A6))`
• 05-15-2019, 11:19 AM
Fluff13
Re: Sumifs returns 0
Alternatively
Formula:
`=SUMPRODUCT((A2:A11=A2)+(A2:A11=A6),B2:B11)`
or
Formula:
`=SUM(SUMIFS(B2:B11,A2:A11,{"320.revenues row","330.revenues - intercompany"}))`
• 05-15-2019, 11:23 AM
Roel Jongman
Re: Sumifs returns 0
The criteria in a SUMIFS function work as AND criteria all criteria have to be met to sum the records that comply

if you want to select 2 values from the samelist then you need to use 2 sumifs (or maybe an array formula)

Formula:
`=+SUMIFS(B2:B11;A2:A11;A2)+SUMIFS(B2:B11;A2:A11;A6)`
• 05-15-2019, 11:40 AM
belinda200
Re: Sumifs returns 0
Thanks. I dont think I understand why my formula doesnt work if both conditions are indeed met.
• 05-15-2019, 11:43 AM
belinda200
Re: Sumifs returns 0
OH I see now see all the comments.
Thansk for all your suggestions and explanations !
• 05-15-2019, 11:44 AM
Fluff13
Re: Sumifs returns 0
For your formula to work col A has to equal both 320.revenues row & 330.revenues - intercompany for the same row.
i.e. A2 would have to equal both values.
• 05-15-2019, 11:45 AM
Melvosh
Re: Sumifs returns 0
You're looking for either condition to be met (OR), not both (AND). What SUMIFS does is require all conditions to be met (AND) at the same time, meaning cells A2:A11 would need to equal both A2 and A6. That's why your formula was returning 0, because both conditions couldn't be met. Hope that explains it! :)
• 05-15-2019, 11:56 AM
Fluff13
Re: Sumifs returns 0
Quote:

Originally Posted by belinda200
Thansk for all your suggestions and explanations !

You're welcome & thanks for the feedback