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.
Answers
-
You would want to use something along the lines of...
=SUMIFS(QuantityMade:QuantityMade, BusinessDate:BusinessDate, @cell = DATE(2020, 05, 18), FloorHour:FloorHour, 5)
-
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?
-
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.
-
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?
-
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.
-
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?
-
This was the formula I was trying to use.
-
Help Article Resources
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
Check out the Formula Handbook template!