SUMIFS Cross referencing Help
I keep getting an "incorrect arguament" or "inparseable" return when trying to sum up the amounts in a given range based on 3 criterion. The sum range & both criteria are in another sheet. The first criterion is a simple "greater than zero" and works well. The second criterion is looking for the word "Approved" and does not work in the formulas i have tried. The third criterion is a check box and I cant get that to work either.
This is what i have so far:
Try 1 = unparseable  using actual values as the criterion:
=SUMIFS({15043222  Change Order Log amount}, {15043222  Change Order Log amount}, >0,{15043222  Change Order Log Status},=Approved,{15043222  Change Order accel},=1)
Try 2 = incorrect argument  using defined references as the criterion:
=SUMIFS({15043222  Change Order Log amount}, {15043222  Change Order Log amount}, >0, {15043222  Change Order Log Status}, {15043222  Change Order Log Approved}, {15043222  Change Order accel}, {15043222  Change Order Log accel true})
Note the referenced sheet is "15043222  Change Order Log", with the columns being 1) "amount" to be summed. 2) "status"  criterion range 1. 3) "Accel"  criterion range 2.
Can you please help me? Im starting to go crazy!
Best Answer

@StaceyT It should work, but all ranges within the formula must be of the same size. If you only selected a specific group of rows within a column to sum, then you would need to also select that same group of rows within your criteria ranges as well.
If you select an entire column for your criteria range, then you would need to select the entire column for the sum range.
Answers

Try this
=SUMIFS({15043222  Change Order Log amount}, >0, {15043222  Change Order Log Status}, "Approved", {15043222  Change Order accel}, "true")
if the column Change Order Accell is a checkbox try this
=SUMIFS({15043222  Change Order Log amount}, >0, {15043222  Change Order Log Status}, "Approved", {15043222  Change Order accel}, 1)

Hello, Thank you.
That column is a checkbox, so I tried the second suggestion:
=SUMIFS({15043222  Change Order Log amount}, >0, {15043222  Change Order Log Status}, "Approved", {15043222  Change Order accel}, 1)
This is returning "invalid operation."

Sorry about that omitted the sum Range
=SUMIFS({15043222  Change Order Log amount}, {15043222  Change Order Log amount}, >0,{15043222  Change Order Log Status}, "Approved" ,{15043222  Change Order accel},1)
it looks like you were very close but when you are looking for Text to be a condition in a formula it always needs to be inside " "

That's ok, I appreciate your help!
Still no luck though, it is returning "Incorrect argument"

this should work
=SUMIFS({15043222  Change Order Log amount}, {15043222  Change Order Log amount}, >0, {15043222  Change Order Log Status}, "Approved", {15043222  Change Order Log accel}, 1)
in the reference sheet does column for Order Log Status have the text "Approved" or is it something else like a checkbox or different text like "approved"

Hi,
it still says "Incorrect argument".
The column for Order log Status has the text "Approved"

could you post a screen grab of three ref columns? I tested the formula above and it worked on my test sheet so something else has to be going on.

Absolutely:

Do you think it could be because there are blank spaces in the reference columns?

The blanks should not be causing a problem. It is strange that you are still getting the error. I can't seem to duplicate the error my end.

Hi,
Just to let you know, I figured out the last piece on why it wasn't working! Turns out there was a problem with the "Amount" column cross referenced as the SUM_RANGE. The range I had defined as the reference was a specific group of cells within the column, but the Smartsheet logic required me to select the entire column (including column header) as the defined reference for the sum_range
I think this may be a glitch in the software as you should not have to select an entire column as the sum range, if you only want to sum up certain cells within that column. When we use sum within the same sheet we are able to select only the certain cells we want, but for some reason this does not work when crossreferencing.
Anyway, thank you very Much RossL for your help with the initial formula! Much appreciated!

@StaceyT It should work, but all ranges within the formula must be of the same size. If you only selected a specific group of rows within a column to sum, then you would need to also select that same group of rows within your criteria ranges as well.
If you select an entire column for your criteria range, then you would need to select the entire column for the sum range.

Hi Paul,
thank you very much for the clarification. I had indeed selected the entire column in other criterion ranges within the formula, because the whole column was needed elsewhere. Still seems like a limitation on Smartsheet's part. vs excel where it is possible to drill down your references. However, I'll make sure they are a/ways the same size/length going forward. Thank you!
Help Article Resources
Categories
Check out the Formula Handbook template!