Help!! Need help with multiple sumifs formula

CBalder

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.

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.

CBalder

@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

@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), "")

Jeff Reisman

@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... 😁

CBalder

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

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

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