CODEWITHSUNDEEP
excelexcel-formula
Nuts
Nuts

Reputation: 2813

SUMIF with OR criteria

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.

Upvotes: 18

Views: 97321

Answers (5)

wama
wama

Reputation: 1

this works with multiple text evaluation

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

Upvotes: 0

kamila
kamila

Reputation: 74

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.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46331

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

Upvotes: 29

Gary's Student
Gary's Student

Reputation: 96753

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

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

Upvotes: 0

ChrisProsser
ChrisProsser

Reputation: 13088

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)

Upvotes: 0

Related Questions