SUMIFS with multiple OR criterion

Mehran Bagherian

Hello there,

I am using the function : SUMIFS( range, criterion_range1, criterion1, [ criterion_range2​, criterion2​... ])

How can I have multiple values for criterion1? I mean, I have multiple values which any one of them will satisfy the requirement, and I want to add all.

E.g. I have a list of many names (in another sheet), each have an amount associated with. Like:

David; 25

John; 23

Sarah; 15

Steve; 46

Mark; 34

In my second sheet, I need to SUMIFS the quantities if the entry is Sarah OR David

Paul Newcome

It would look something like this...

=SUMIFS({Reference Sheet Number Column}, {Reference Sheet Name Column}, OR(@cell = "David", @cell = "Sarah"))

Mehran Bagherian

Hi Paul,

It worked, thank you.

Is there anyway that I can use another reference for David and Sarah here? I mean instead of typing "Davis" and "Sarah" in this formula, I refer another cell or a range of cells?

In my case, I have a column of names, including "David" and "Sarah", and this list may change.

Paul Newcome

Yes. You would use direct cell references in place of the specific text.

=SUMIFS({Reference Sheet Number Column}, {Reference Sheet Name Column}, OR(@cell = [Column Name]1, @cell = [Column Name]2))

Mehran Bagherian

I mean something like:

OR(@cell=[Column Name]1:[Column Name]10) to bring a range, or

OR(@cell=[Column Name]) whole column in one shot.

but it doesn't work with these code

Paul Newcome

Are you able to provide a screenshot for reference? I'm not sure I follow.

Mehran Bagherian

=SUMIFS({Refsheet Range 1}, {Refsheet Range 2}, OR(@cell[Team1:Team10], {Refsheet Range 3), Project@row)

Here is the screenshot.

I am going to have a sum of hours in Refsheet, which is spent on project 1, by anyone of Team members.

Paul Newcome

My suggestion would be to sum by person and then just total the column containing the sums.