Help!! Need help with multiple sumifs formula

CBalder
CBalder ✭✭
edited 06/15/22 in Formulas and Functions

I need multiple sum ifs function help. Right now I have the * I believe* correct formula to look up 1 item in a column and it is providing the right output. But I need it to look up multiple/search for multiple in a column.

Current Formula:

=SUMIFS([Total of Previously Approved Change Orders]@row + [Change Order Amount]@row, [Branch Number]@row, "5005", [Approved / Denied]@row, "Approved")


I need it to look up multiple different 'branch numbers' (bolded) , so for example I would need it to look up 5005, or 2008, or 2014, etc. There are multiple drop down options in this column that I need summed if it matches.

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @CBalder

    I am not sure what is going on at the start of this formula with "[Total of Previously Approved Change Orders]@row + [Change Order Amount]@row" however to use multiple criteria you would need to use the OR function and @cell:

    =SUMIFS([Total of Previously Approved Change Orders]@row + [Change Order Amount]@row, [Branch Number]@row, OR(@cell = "5005", @cell = "2008", @cell = "2014"), [Approved / Denied]@row, "Approved")

    This tells SUMIFS to consider the cell in [branch Number]@row and see if it equals any of the values you list within the OR statement.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • CBalder
    CBalder ✭✭
    edited 06/15/22

    @Jeff Reisman Thanks so much for your response! The beginning of the formula is to sum two columns together (screen shot below to show them highlighted). Perhaps I have this wrong.

    I am looking to have the two highlighted columns summed if the branch number is one listed and the "approved/denied" column is approved.

    When I put in the 'or' I am getting an #UNPARSEABLE return



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @CBalder Ok, that makes more sense now.

    You don't need SUMIFS here, you need an IF statement with AND and OR embedded. The logic is:

    If the branch number is one of these values, and the "approved/denied" column is approved, then add the values from these two columns together.

    The syntax for IF is: =IF(logical expression, value if true, value if false)

    In this case, the logical expression will get a little wild, because we have several criteria to consider. The AND says the evaluate if the expression [Approved / Denied]@row = "Approved" is true, and at least one of the expressions inside the OR statement is true, then complete this math of adding these two other column values together, otherwise, leave the cell blank.

    =IF(AND([Approved / Denied]@row = "Approved", OR([Branch Number]@row = "5005", [Branch Number]@row = "2008", [Branch Number]@row = "2014")), ([Total of Previously Approved Change Orders]@row + [Change Order Amount]@row), "")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 06/15/22

    @CBalder

    Right after I posted the above, I thought of a way you could use SUMIFS. Just turn [Total of Previously Approved Change Orders]@row + [Change Order Amount]@row into a range, by replacing the " + " with : as follows.

    =SUMIFS([Total of Previously Approved Change Orders]@row:[Change Order Amount]@row, [Branch Number]@row, OR(@cell = "5005", @cell = "2008", @cell = "2014"), [Approved / Denied]@row, "Approved")

    Logic: Sum the values in this horizontal range of cells, if these criteria are true. This works as long as the [Total of Previously Approved Change Orders] column is directly next to and to the left of the [Change Order Amount] column.

    This should totally work... 😁

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • CBalder
    CBalder ✭✭

    @Jeff Reisman This worked!!! Thank you so much!!!! Saved me hours of wracking my brain!

  • CBalder
    CBalder ✭✭

    @Jeff Reisman So sorry are you able to help one more time! I am trying to sum the Change Order amount row with the same logic. If branch number is one of 5005, 2008, etc. and the approved/denied is approved, sum the approved change orders in that column. I tried to manipulate your or statement but nothing is working. Am I doing something wrong?



  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @CBalder With the above, you are only giving the SUMIFS a single cell ([Change Order Amount]@row) to add up. It can't do that. SUMIFS requires a range of cells to add up; either all or part of a column of cells, part of a row, or a selection of columns and rows.

    If you were trying to add up all the values in the Change Order Amount column where the row meets the criteria, you would give it the entire column range. This is done by listing the column name, a colon, and the column name again:

    =SUMIFS([Change Order Amount]:[Change Order Amount], [Branch Number]@row, OR(@cell = "5005", @cell = "2008", @cell = "2014"), [Approved / Denied]@row, "Approved")

    In my signature there is a link to the Functions List, with help pages for each. There's also a template in Smartsheet called Formula Examples which you can add to your sheet list. Very helpful resources.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!