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!

Find more posts tagged with

Accepted answers

Paul Newcome

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

All comments

RossL

@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

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

@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

That's ok, I appreciate your help!

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

RossL

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

Hi,

it still says "Incorrect argument".

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

RossL

@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

Absolutely:

StaceyT

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

RossL

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

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!

Paul Newcome

If you select an entire column for your criteria range, then you would need to select the entire column for the sum range.

StaceyT

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.