SUMIF Not Working?

Jessb9187

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

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