SUMIFS with multiple OR criterion
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
Answers
-
It would look something like this...
=SUMIFS({Reference Sheet Number Column}, {Reference Sheet Name Column}, OR(@cell = "David", @cell = "Sarah"))
-
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.
-
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))
-
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
-
Are you able to provide a screenshot for reference? I'm not sure I follow.
-
=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.
-
My suggestion would be to sum by person and then just total the column containing the sums.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives