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

«1

Answers

  • Paul Newcome
    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)

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • In a column to the right of quantity made

  • NikkiOno
    NikkiOno ✭✭✭✭
    edited 10/19/22

    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
    NikkiOno ✭✭✭✭
    edited 10/19/22

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • NikkiOno
    NikkiOno ✭✭✭✭
    edited 10/19/22

    well nevermind. I figured it out :)

  • NikkiOno
    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
    Paul Newcome ✭✭✭✭✭✭

    @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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    @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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • This was the formula I was trying to use.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!