How can I use SUMIFS (or another function) to sum values if two rows match each other?
I am trying to add together the total quantity made on a certain date and time. For example, in the screenshot, I have two rows that contain 5/18/20 that occur on floorhour 5. Is there a formula I can write to add together the value from a column called QuantityMade only if the date and floor hour match?
Thanks!
Best Answer
-
Here is what I am going to suggest...
Insert a new text/number type "helper" column. In this column enter:
=IF(COUNTIFS(BusinessDate$1:BusinessDate@row, BusinessDate@row, FloorHour$1:FloorHour@row, FloorHour@row)> 1, SUMIFS(QuantityMade:QuantityMade, BusinessDate:BusinessDate, @row, FloorHour:FloorHour, FloorHour@row), QuantityMade@row)
The above will see if there are any rows that have the same date and floor hour and will add them together in the row of the first instance. The rest of the matching rows will remain blank. If there is only one entry for that date/floor hour, then it will pull the quantity over.
Then you can reference this "helper" column in your other formulas.
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!
Answers
-
You would want to use something along the lines of...
=SUMIFS(QuantityMade:QuantityMade, BusinessDate:BusinessDate, @cell = DATE(2020, 05, 18), FloorHour:FloorHour, 5)
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!
-
Thanks Paul! Is there a way to write the formula to SUM for any match combination without changing the formula parameters? For example, if on 5/20/20 there were two rows that contained floor hour 7. Can the formula be written universal for any date / floor hour that matched?
I came up with a pretty lengthy solution using join function in one column and then sumif function in the next column to identify matches. Not sure if there is a simper solution.
-
Where exactly are you putting the formula?
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!
-
In a column to the right of quantity made
-
Here is what I am going to suggest...
Insert a new text/number type "helper" column. In this column enter:
=IF(COUNTIFS(BusinessDate$1:BusinessDate@row, BusinessDate@row, FloorHour$1:FloorHour@row, FloorHour@row)> 1, SUMIFS(QuantityMade:QuantityMade, BusinessDate:BusinessDate, @row, FloorHour:FloorHour, FloorHour@row), QuantityMade@row)
The above will see if there are any rows that have the same date and floor hour and will add them together in the row of the first instance. The rest of the matching rows will remain blank. If there is only one entry for that date/floor hour, then it will pull the quantity over.
Then you can reference this "helper" column in your other formulas.
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,
I am looking to do something similar but my formula isnt working. I need to sum net sales from a different sheet (net sales sheet) if the sales associate's name matches on the dashboard data sheet and net sales sheet. Can you combine sumif and match in the same formula referencing another sheet?
I have tried these two formulas and cant get either to work.
=SUMIFS({Net Sales}, {Daily Sales Activity Range 3}, "yes", {Daily Sales Activity Range 1=[Primary Column]@row})
=SUMIF({Daily Sales Activity Range 3}, "yes", {Net Sales}, MATCH([Primary Column]@row, {Daily Sales Activity Range 1}, 0))
-
@Paul Newcome well nevermind. I figured it out :)
-
@NikkiOno Glad you were able to get it sorted. It looks like maybe your first one was just some syntax errors?
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!
-
well nevermind. I figured it out :)
-
@paul newcome Yep. This is what worked: =SUMIFS({Net Sales}, {Daily Sales Activity Range 3}, "yes", {Daily Sales Activity Range 1}, [SALES ASSOCIATE]@row)
-
@NikkiOno Glad it was something relatively simple and not something to crazy.
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 have two sheets. The first sheet collects registration data. The 2nd Sheet is totaling how many people have registed for a certain date & time slot. I can not figure out the formula to look up the specific date & time and if there are registrations count how many people. Any help would be greatrly appreciated.
This sheet shows 2 registrations for 4/29 totaling 10 guests
This is the sheet that I would like to display 10 as the total # of guests registered for 4/29
Can you please advise as to what the correct formula would be to return a value of 10 under # Of Guests Attending?
-
@Kelly Mo You would need a SUMIFS. Are you able to provide the formula currently outputting the error?
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!
-
This was the formula I was trying to use.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 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
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!