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

Answer 2

I don't think there is a way to do OR within a single statement like this. You can use SUMIFS for multiple conditions where all need to be true, but in this case you would just need to add together multiple SUMIF statements:

=SUMIF(A4:A100,"1",B4:B100)+SUMIF(A4:A100,"2",B4:B100)

Answer 3

Since "1" and "2" are mutually exclusive:

=SUMIF(A4:A100,"1",B4:B100)+SUMIF(A4:A100,"2",B4:B100)

Answer 4

i think you should define a range, let's say keys where you keep all values for which you want to sum. so in this range you keep 1 and 2 and can modyfy it whenever you want. then you add a flag column with formula IFERROR(IF(MATCH(A4,keys,0)>0,1,0),0) - now you have column in which 1 is for the values you want to sum.

Answer 5

this works with multiple text evaluation

=sumif(M4:M206,"Sat",O4:O206)+sumif(M4:M206,"Sun",O4:O206) // add here more + + +