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"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
=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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 33 Webinars
- 7.3K Forum Archives