SUMIF Not Working?

Jessb9187 · 06/06/19

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?

Nick Burrus · 06/06/19

Have you tried using [ over {?

If you share your Smartsheet to me at[email protected]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 · 06/06/19

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 · 06/06/19

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 · 06/06/19

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 SawyerConsultant & Training Manager

Jessb9187 · 06/06/19

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 · 06/06/19

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.

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

Paul Newcome · 06/06/19

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

SUMIF Not Working?

Comments

Categories