SUMIFS with multiple criteria in criteria1

[Dan27]

Hello

When using SUMIFS is there a way you can set the criteria to be one of multiple values?

So in the example below I might want my criteria to be peter or paul. If I put it as a second criteria it has to meet both conditions.

=SUMIFS(B:B,A:A,"Peter")

So where Peter appears how can I say Peter or Paul? Or even better a range of cells


A B
Peter 5
John 6
Paul 7

Thanks

[ragulduy]

Maybe something like;
=SUM(SUMIFS(B:B,A:A,{"Peter","Paul"}))
?
or for a range of cells, I think you might be able to do:
=SUM(SUMIFS(B:B,A:A,C1:C2))
as an array formula (confirm with ctrl+shift+enter)

[Dan27]

Thanks

{=SUMIFS(A:A,B:B,C1:C3)} Doesn't work. I just tried. It only returns the value of C1 and not C1+C2+C3

[romperstomper]

You misread - it's:
=SUM(SUMIFS(A:A,B:B,C1:C3))

[XOR LX]

SUMPRODUCT is also fine:

=SUMPRODUCT(SUMIFS(A:A,B:B,C1:C3))

Regards

[Dan27]

That works great.

Only thing is where C1:C3 appears do these have to be in an array? I can't specify C1,C3 can I?

[XOR LX]

You mean you want to be able to choose which elements from C1:C3 are included? In that case, can't you just use the array constant form (as given in post #2) and type your criteria in?

I could give you a formula which will work for the range C1:C3, but just include whichever elements of that range you want to include, e.g the 1st and 3rd, though you'd still have to manually go into the formula to specify which cells to include every time you wanted to change the cells to be included.

Regards

[ragulduy]

I'm not sure, if it's just those two you could try:
=SUMIFS(B:B,A:A,C1)+SUMIFS(B:B,A:A,C3)

[Dan27]

It would be more than just those 2. It's quite a few cells but I simplified it for my example. XOR XL which formula would you use?

[ragulduy]

I would suggest using the formula in posts 2-5 and changing your data so they are in a continuous set of cells.

[XOR LX]

=SUMPRODUCT(SUMIFS(A:A,B:B,INDEX(C1:C3,N(IF(1,{1,3})))))

Change the {1,3} as required.

Regards