SumIF errors

Ian Smith 2017

I have a very simple formula in one of my sheets, eg:

=SUMIF(Type251:Type271, "Service", Value251:Value271)

and it works fine, the last row on the table is row 266.

however if I extend the formula to =SUMIF(Type251:Type281, "Service", Value251:Value281)

essentially just including more empty cells, I get an unparsable error.

Any ideas? It's driving me mad

Paul Newcome

To expand on the above...

Smartsheet automatically adds 10 rows to the bottom of every sheet. If your last row is 266, then your sheet will have 276 rows. Referencing row 277+ will throw the error because those rows do not exist on your sheet.

Ian Smith 2017

Sometimes it's the simplest things... Thanks Paul.

Matt Johnson

@Ian Smith 2017

I tried to recreate the error but I couldn't. Your formula worked in my test sheet. Could you send a snip of the 2 columns?

Ian Smith 2017

So having added a few more lines into the sheet yesterday, I thought I'd try extending the formula again this morning, it appears that if it is extended any more than 15 lines past the last line with data the unparsable error appears. Some sort of limitation with smartsheet perhaps?

Paul Newcome

Does your sheet actually have that many rows on it (empty or otherwise)?

Paul Newcome

To expand on the above...

Smartsheet automatically adds 10 rows to the bottom of every sheet. If your last row is 266, then your sheet will have 276 rows. Referencing row 277+ will throw the error because those rows do not exist on your sheet.

Ian Smith 2017

Sometimes it's the simplest things... Thanks Paul.

Paul Newcome

Happy to help! 👍️