Help with SUMIFS function

Try
=SUM(SUMIFS(C$2:C$11,A$2:A$11,D2,B$2:B$11,{"apple","banana"}))

Hi Jonmo1,

Thank you very much for replying, the formula partly works but it also sums when there is an ID with "apple" and not "banana".
I would like it to sum the values of "apple" & "banana" when both are present within the same ID, if only one is present the result should be 0.

So in the example the results I am after in cells E2 to E5 are:

500
0
0
600

I've tried tweaking your formula but with no luck, can you help?

Thank you very much

Try

=IF(SUM(--(COUNTIFS($A$2:$A$11,D2,$B$2:$B$11,{"apple","banana"})>0))=2,SUM(SUMIFS(C$2:C$11,A$2:A$11,D2,B$2:B$11,{"apple","banana"})),0)

Thank you Jonmo1 that's great!

I was trying with IF, AND and OR functions but didn't consider your solution with COUNTIFS, which seems very neat and works very well! Thank you!

I do have one question about how the formula is working if that's ok?

For instance if I have an ID with two "apple" entries and no "banana", giving an array {2,0} how does that change to {TRUE,FALSE}?

I get that the double dash changes it from {TRUE,FALSE} to {1,0}.

Thank you

Thank you very much for the explanation!

Of course the operator greater than is what changes the countifs to True or False, it makes perfect sense, and very useful to see how the syntax is working.

A very clever simple formula and great to see how it is put together, that really helps with my understanding of formulas in excel.

Thank you again!

Thank you very much for the explanation!

Of course the operator greater than is what changes the countifs to True or False, it makes perfect sense, and very useful to see how the syntax is working.

A very clever simple formula and great to see how it is put together, that really helps with my understanding of formulas in excel.

Thank you again!

You're welcome.