Multiple Criteria #incorrect argument error

Andrew Taglauer

I need help with this formula. I have multiple criteria columns that need to find the sum of the amount in all rows in the sheet and still getting #incorrect argument error.

=SUMIFS({Change Order-Submitted}, (HAS(@cell) = "CO- Not Submitted", {Tracker-Assigned To}, "Manager"))

Kelly Moore

Hey @Andrew Taglauer

The syntax in your formula isn't the one smartsheet is expecting for SUMIFS().

=SUMIFS({Change Order-Submitted}, {Change Order-Submitted}, HAS(@cell,"CO- Not Submitted"), {Tracker-Assigned To}, "Manager")

I noticed you are using the HAS function. Is your Change Order submitted column a multi-select column (multiple responses can be in the same cell vs only one response will ever be in a single cell). If this is not a multi-select then the HAS function is not needed and would be the same format as your Assigned To column

Will this work for you?

Kelly

Andrew Taglauer

@Kelly Moore Thanks for the quick response. That cleared the error but did not return the sum amounts I was looking for in the sheet. Do I also need to include that column as well?

Here is an example of the sheet that I am working on from my metrics rollup. I need to be able to sum column2 amounts for each item in the primary column. So If any 1 is located in the primary column and has CO-Not Submitted and Manager there would be a $8.00 return.

Kelly Moore

Hey @Andrew Taglauer

Here is the formula just for the SUMIFS portion of what I think you are asking for. Let's make sure we get that piece working first. I'm flipping back to a SUMIFS instead of the singular version of SUMIF as SUMIF only works for a single criteria. SUMIFS works with one to whatever number of criteria. It is by far the more versatile of the two formulas and my personal preference is to always use SUMIFS vs the singular version.. Also, when using any of the SUM functions you must be summing a column that has numeric data in it.

Since your sheet is set up with Parent-Child hierarchy, we use that to collect only the needed data. By first checking if the current row is a Parent row, this formula can be converted to a column formula

=IF(COUNT(CHILDREN([Column2]@row)) > 0, SUMIFS(CHILDREN([Column2]@row), CHILDREN([Tracker Assigned To]@row), "Manager", CHILDREN([Change Order-Submitted]@row), "CO-Not Submitted"))

Will this work for you?

Kelly

Andrew Taglauer

@Kelly Moore That worked and appreciate the context for SUMIF vs SUMIFS. Its definitely been a learning experience for me.