How to use SUMIF formula in Excel cell that must sum over a given range and instead of finding for a single value, it should find multiple values?
For finding a single value, I use:
=SUMIF(A4:A100;"1";B4:B100)
Now I need to sum over if the column A holds 1 or 2, like:
=SUMIF(A4:A100;"1" OR "2";B4:B100)
The cell A1 will hold the criteria as a text, here it would be 1;2.
It should return same as
=SUMIF(A4:A100;"1";B4:B100) + SUMIF(A4:A100;"2";B4:B100)
but I need a formula that can take any number of criteria (1,2,3,... or more).
What's the syntax? I'm not able to use VBA here.
Answer
To sum for 1 or 2 try this version
=SUM(SUMIF(A4:A100;{1;2};B4:B100))
SUMIF will return an "array" of two results so you need SUM to sum that array for the total for 1 and 2
You can add as many numbers as you like e,g,
=SUM(SUMIF(A4:A100;{1;2;3;4};B4:B100))
or with numbers listed in a range like Z1:Z10
=SUMPRODUCT(SUMIF(A4:A100;Z1:Z10;B4:B100))