How can I use SUMIFS (or another function) to sum values if two rows match each other?

Amy Balich

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!

Paul Newcome

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.

Paul Newcome

You would want to use something along the lines of...

=SUMIFS(QuantityMade:QuantityMade, BusinessDate:BusinessDate, @cell = DATE(2020, 05, 18), FloorHour:FloorHour, 5)

Amy Balich

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.

Paul Newcome

Where exactly are you putting the formula?

Amy Balich

In a column to the right of quantity made

Paul Newcome

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.

NikkiOno

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))

NikkiOno

@Paul Newcome well nevermind. I figured it out :)

Paul Newcome

@NikkiOno Glad you were able to get it sorted. It looks like maybe your first one was just some syntax errors?

NikkiOno

well nevermind. I figured it out :)

NikkiOno

@paul newcome Yep. This is what worked: =SUMIFS({Net Sales}, {Daily Sales Activity Range 3}, "yes", {Daily Sales Activity Range 1}, [SALES ASSOCIATE]@row)

Paul Newcome

@NikkiOno Glad it was something relatively simple and not something to crazy.

Kelly Mo

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?

Paul Newcome

@Kelly Mo You would need a SUMIFS. Are you able to provide the formula currently outputting the error?

Kelly Mo

This was the formula I was trying to use.

Paul Newcome

@Kelly Mo You are going to need a SUMIFS.

https://help.smartsheet.com/function/sumifs