SUMIF Not Working?

Hello again,

I am using a pretty basic formula which I've used countless times, even yesterday, and now I'm getting an #unparseable error.

I need to add the number of onsite days (range) if the visit type (criterion) is listed as a conversion. My formula looks like this:

=SUMIF({KPI: Visit Types &Days Onsite Remainder 20 Range 1}, {KPI: Visit Types and Days Onsite Range 1} "Conversion")

I'm not sure what is wrong with this formula. Any ideas on this one?

Find more posts tagged with

Comments

Nick Burrus

Have you tried using [ over {?

If you share your Smartsheet to me at NBurrus@stria.com I'm happy to take a look at it. Your formula doesn't make a lot of sense. There's no commas fragmenting what it is to look for in the criteria. https://help.smartsheet.com/function/sumif

Thanks

Nick

Paul Newcome

Switching the curly brackets to square brackets will not work. Square brackets are used for column names that have a number, space, and/or a special character. Curly brackets are used for cross sheet references.

It looks to me like two different sheets are being referenced. Is that correct?

You are also missing a comma between the criteria range and the criteria.

Jessb9187

It was the same sheet. I'm not sure why the reference for the criteria was pulling a different name. I've updated my formula and now it's pulling the same sheet name at least. This one, gives me an incorrect argument set.

=SUMIF({KPI: Visit Types &Days Onsite Remainder 20 Range 2}, {KPI: Visit Types &Days Onsite Remainder 20 Range 1}, "Conversion")

Debbie Sawyer

Hi - I think your order of arguments is incorrect in the last example...Syntax for a SUMIF function is

=SUMIF(RANGE, CRITERION, [SUM RANGE])

In the example above it appears to be in the order of

=SUM([SUM RANGE], RANGE, CRITERON)

Try reordering your arguments to match this instead:

=SUMIF({KPI: Visit Types &Days Onsite Remainder 20 Range 1}, "Conversion",{KPI: Visit Types &Days Onsite Remainder 20 Range 2})

Hope this helps...

Kind regards

Debbie Sawyer Consultant & Training Manager

Jessb9187

So I figured it out. Apparently you need a SUMIFS not a SUMIF formula. Updated formula:

=SUMIFS({KPI: Visit Types &Days Onsite Remainder 20 Range 2}, {KPI: Visit Types &Days Onsite Remainder 20 Range 1}, "Conversion")

Paul Newcome

Your syntax is correct. here are a few things to look at:

1. Click into the formula, click into the middle of the range reference, and then select "Edit Range". Give it a minute to load up.

Sometimes when I go to reference another sheet, if I select my range too quickly it will be before the sheet has had a chance to truly load and the range reverts to the top left cell.

2. Both ranges need to be the same size. Since you are referencing another sheet, you should be able to select the entire column by clicking on the column header when establishing the range.

3. Make sure your ranges are in the right order within your formula. The first range is the one you want to SUM, and the second range should be the range that contains the cells that would be housing your criteria.

Your overall syntax (without being able to see the ranges themselves) is correct though. Parenthesis, commas, quotes, etc. are all where they should be.

Based on the incorrect argument error, my first guess would be the range sizes not matching (#2 which could be a result of #1)

Paul Newcome

Ugh! Good catch on the syntax. I use SUMIFS exclusively and was getting it completely backwards. ?

Sign in to join the conversation!

Quick Links

Trending discussions

Spanish translation
Does anyone know if there is a way within Smartsheet to create translated versions of the sheet and the form? I really need English and Spanish but I would love for the data to feed into the same sheet which would be English. Is that even possible? Freda
Updating of cells from an approval form submission
I have a sheet of users listed and a field to confirm if they are enabled or disabled. Then I have a second sheet populated from a form, where users submit a request to have access enabled. this uses an automated function to send an approval email to the manager who approves or rejects. What I would like to do is use this…
I just lost all my filters and conditional formatting. Is there something happening on the backend?
I just lost all my filters and conditional formatting. Is there something happening on the backend?