Formula not working as in instructions.

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

https://help.smartsheet.com/function/sumif?frame=0&nav=1

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.

Screen Shot 2020-05-04 at 12.31.59 PM.png

Screen Shot 2020-05-04 at 12.41.22 PM.png

Find more posts tagged with

Formulas

functionality

Accepted answers

All comments

Andrée Starå

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!

Paul Pindell

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.

Screen Shot 2020-05-05 at 9.47.51 AM.png

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

Paul Newcome

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

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

Vu Nguyen Anh

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.

Paul Newcome

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)

Paul Pindell

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.

Andrée Starå

@Paul Pindell

You're more than welcome!

Thanks for sharing!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.