excel - SUMIF with OR criteria

Question

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 1

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))