SUMIFS won't work, count doesn't match column count

Katy Brown

I have a SUMIF formula, where I am counting the sum of percents if the Stage equals certain stages (1-5)

=SUMIFS({Stage Percent}, {Stage}, [Status]@row)

Inexplicably, today, the formula broke and is giving a "INCORRECT ARGUMENT SET" error message.

The EA Stage Percent is fine, because when I remove the Stage and Status @ row, it presents a sum with no error.

I then counted how many instances of Stage Percent (288) and how many of Stage (287).

So clearly the error is because Stage doesn't have the same amount of lines as Stage Percent.

However, when I go to the source page and highlight the column, it says that Stage has a count of 288!

I have no idea why my formula says 287, and the actual sheet says 288.

I've tried backing out of the instance by signing out and closing Smartsheets, then reopening - the error persists. Is this a glitch?

Nik Fuentes

@Katy Brown

There's a non-zero chance that this is a glitch of some kind. I have had it before where Sartsheets chokes on a perfectly functional formula for no reason until I recreate the formula in another cell [exactly the same I might add]. A couple of questions before I assume that's what it is though.

For the out-of-sheet references, are you referencing a designated range (e.g. [Column1]5:[Column1]25) or are you referencing an entire column (e.g. [Column1]:[Column1])? If the latter, is there a summary or header row in that range? If so it may be counting that and giving you 1 extra value. Additionally, the count in the bottom right on a highlighted range only counts populated cells, so if you have a range of 288 cells selected, but 1 is empty, it will say 287. If this is the case, there may be a missing value [especially at the top or bottom] that is confusing smartsheets. Have you given the range a check recently? [you probably have, but it's worth checking again.]

Or, you know, it is just bugged. Smartsheets will sometimes hit a formula or a cell it can't deal with for some unknown reason and will just refuse to even try no matter what you do to 'fix' it; there's typically no way to tell this is happening until you've exhausted all of the solutions you could think of. Before you start doing anything drastic, I recommend recreating the formula [don't copy/paste it, retype it out from scratch, redesignating the ranges and everything] in another row and column, then save and refresh the page. Sometimes it is just that easy [and just that infuriating].

if that doesn't work... ¯\_ (ツ)_/¯ I'd need to look at the references directly to say anything more, though maybe somebody else has an idea I'm not thinking of.

Nik Fuentes

@Katy Brown

There's a non-zero chance that this is a glitch of some kind. I have had it before where Sartsheets chokes on a perfectly functional formula for no reason until I recreate the formula in another cell [exactly the same I might add]. A couple of questions before I assume that's what it is though.

For the out-of-sheet references, are you referencing a designated range (e.g. [Column1]5:[Column1]25) or are you referencing an entire column (e.g. [Column1]:[Column1])? If the latter, is there a summary or header row in that range? If so it may be counting that and giving you 1 extra value. Additionally, the count in the bottom right on a highlighted range only counts populated cells, so if you have a range of 288 cells selected, but 1 is empty, it will say 287. If this is the case, there may be a missing value [especially at the top or bottom] that is confusing smartsheets. Have you given the range a check recently? [you probably have, but it's worth checking again.]

Or, you know, it is just bugged. Smartsheets will sometimes hit a formula or a cell it can't deal with for some unknown reason and will just refuse to even try no matter what you do to 'fix' it; there's typically no way to tell this is happening until you've exhausted all of the solutions you could think of. Before you start doing anything drastic, I recommend recreating the formula [don't copy/paste it, retype it out from scratch, redesignating the ranges and everything] in another row and column, then save and refresh the page. Sometimes it is just that easy [and just that infuriating].

if that doesn't work... ¯\_ (ツ)_/¯ I'd need to look at the references directly to say anything more, though maybe somebody else has an idea I'm not thinking of.

Katy Brown

https://community.smartsheet.com/discussion/comment/303416#Comment_303416

Okay! It seems to have fixed itself with the method you suggested. I deleted the formula and re-entered it piece by piece and it corrected itself. I'll remember this for future errors, thank you so so much for your help!