SUMIFS Cross referencing Help

Options

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

Answers

  • RossL
    RossL ✭✭✭✭✭✭
    edited 07/10/20
    Options

    @Stacey Turnbull

    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)

  • StaceyT
    Options

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

  • RossL
    RossL ✭✭✭✭✭✭
    Options

    @Stacey Turnbull

    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 " "

  • StaceyT
    Options

    That's ok, I appreciate your help!


    Still no luck though, it is returning "Incorrect argument"

  • RossL
    RossL ✭✭✭✭✭✭
    edited 07/10/20
    Options

    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"

  • StaceyT
    Options

    Hi,


    it still says "Incorrect argument".


    The column for Order log Status has the text "Approved"


  • RossL
    RossL ✭✭✭✭✭✭
    edited 07/10/20
    Options

    @StaceyT

    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.

  • StaceyT
    Options

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

  • RossL
    RossL ✭✭✭✭✭✭
    Options

    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.

  • StaceyT
    StaceyT
    edited 07/15/20
    Options

    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 cross-referencing.


    Anyway, thank you very Much RossL for your help with the initial formula! Much appreciated!

  • StaceyT
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!