# Formula not working as in instructions.

Options

I am trying to follow the instructions on the SUMIF formula page.

I created a sheet with only the "Sold Date" and the "Transaction Total" columns, entered some data, and I copied the formula "=SUMIF([Sold Date]1:[Sold Date]3, MONTH(@cell) = 2, [Transaction Total]1:[Transaction Total]3)" and pasted into a Summary Sheet field as a formula.

I receive back an #UNPARSEABLE error.

Thoughts? I am trying to build a more complex formula but I cannot get the simple examples to work as written. So I am unsure if it is my formula or something else.

Tags:

## Answers

• ✭✭✭✭✭✭
Options

Hi Paul,

You’ll have to use the SUMIFS function, and it’s structured a little differently.

Try something like this.

=SUMIFS([Transaction Total]:[Transaction Total]; [Sold Date]:[Sold Date]; IFERROR(MONTH(@cell); "") = 2)

The same version but with the below changes for convenience.

=SUMIFS([Transaction Total]:[Transaction Total], [Sold Date]:[Sold Date], IFERROR(MONTH(@cell), "") = 2)

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Andrée,

Thank you for the reply. I've copied and pasted both of those formulas into my sheet, and I still get #UNPARSEABLE. But if I type in the formula with the commas, character by character, it works.

A side question: Do the docs on the SUMIF page need to be updated? If I need a SUMIFS command rather than a SUMIF command to do this should the example on the SUMIF page be removed?

I've also noticed an oddity. I can copy and paste a formula from one field into another on the same sheet summary, and the original one functions as desired, but the copied one comes up #unparseable. Is copy/paste not a method of entering formulas into sheet summary fields I ought to use?

Paul

• ✭✭✭✭✭✭
Options

You could have continued to use the SUMIF. The problem was that you had spaces in the middle of your ranges.

You had

[Sold Date]1 :[Sold Date]3

vs

[Sold Date]1:[Sold Date]3

It looks the same with the [Transaction Total] range as well.

There was also the problem of your dates not being in a date type column. Initially you had your dates in the Primary Column which is only a text/number type. The MONTH function would have errored out once you fixed the spacing issue in your ranges because your dates were text strings instead of actual dates.

• Options

Hi,

I have the same problem with @cell in month() formula that is not working.

=IFERROR(COUNTIF(Date\$1:Date\$12, MONTH(@cell) = Month@row), 0)

I have tried again and again but all return with #INVALID DATA TYPE error.

Pls help on this issue.

• ✭✭✭✭✭✭
Options

It is because of the blank cells in the Date column. Try this instead. Move the IFERROR to the MONTH function.

=COUNTIFS(Dates\$1:Dates\$12, IFERROR(MONTH(@cell), 0) = Month@row)

• Options

Paul et al.,

Thank you for your reply. I worked with Support and there does seem to be a reproducible issue with copy/pasting formulas into Smartsheet in Safari on Mac. I filed support case 03949189, and was told this was confirmed to be a bug.

In the end there was nothing wrong with my formulas, rather the copy/paste functionality in Safari was introducing a hidden character at the end of everything I pasted. When I switched to using chrome for copy and pasting formulas everything simply worked. I was given a workaround of copy>paste> then hit the forward delete key before hitting enter. this also seemed to work.

• ✭✭✭✭✭✭
Options

You're more than welcome!

Thanks for sharing!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!