Sum formula not working

I want to sum all the values in the yellow columns, however it's not happening with the usual sum function🤷‍♀️

The values in the yellow columns are all from calculations of IF(AND()) formula, as below:

=IF(AND([Price Type]@row = "Regular", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1800, "") + IF(AND([Price Type]@row = "Early Bird", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1500, "") + ...

And the sum formula is just a simple:

=SUM([23rd Total Radiology 30Jan-2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan - 2 Feb Price AED (VAT Ex)]@row)

I have been using these formula before, but this is the 1st time i encountered this issue. I need a hand :)

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you able to provide screenshots of the formulas within the sheet similar to the screenshot below?

Donna B.

Hi @Paul Newcome:

If I select individually (not a range), it gives me this:

Christian G.

It is interpreting the cells as String and not there value.

Try this:

Chage the "IF " satatement. Instead of

IF(AND([Price Type]@row = "Regular", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1800, "") +....

IF(AND([Price Type]@row = "Regular", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1800, 0) +...

I think that the summation refuse to add empty strings with integer.

Paul Newcome

Since you want those blank ones left blank, I would remove the third portion of each IF statement altogether.

=IF(AND([Price Type]@row = "Regular", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1800) + IF(AND([Price Type]@row = "Early Bird", [23rd Total Radiology 30 Jan - 2 Feb]@row = 1), 1500) + ...

This will leave the cell empty (as opposed to a blank being treated as a text value).

Another option would be to adjust your SUM formula to a SUMIFS to only pull in cells where there is an actual number value:

=SUMIFS(range, range, ISNUMBER(@cell)

=SUMIFS([23rd Total Radiology 30Jan-2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan - 2 Feb Price AED (VAT Ex)]@row, [23rd Total Radiology 30Jan-2Feb Price AED (VAT Ex)]@row:[All Access Pass 30 Jan - 2 Feb Price AED (VAT Ex)]@row, ISNUMBER(@cell))

Donna B.

Hi @Christian G. & @Paul Newcome ,

Thanks for your response. I did play around with the formula. If I put "(...,1800,0)" this would give 0 values like 0080000 and leave the blanks with varying number of zeros. (sorry forgot to screenshot).

If I remove entirely the third portion, this still leaves zeros in the blank cell

But this is better than where I started, but aesthetically I prefer to leave the zero cells blank😅. Might there be a way to do this?

Christian G.

In your Gigantic "If" summation, is it possible that only one of them is true at a time?

If so, I would build a Price Reference Table like so:

And then only refere the price in a single IF

=If([23rd Total Radiology 30 jan - 2 feb]@row=1,Index({PriceRef},Match([Price Type]@row,{PriceTypeRef,0)))

It is not directly solving your issue, but it is still improvment.

Christian G.

I think I found why it is originaly not working.

Let's evaluate the formula.

=IF(AND([Price Type]@row="Regular",[23rd Total Radiology 30 jan - 2 feb]@row=1)1800,"")+IF(AND([Price Type]@row="Early Bird",[23rd Total Radiology 30 jan - 2 feb]@row=1)1500,"")+IF(AND([Price Type]@row="SP1",[23rd Total Radiology 30 jan - 2 feb]@row=1)1000,"")+IF(AND([Price Type]@row="SP2",[23rd Total Radiology 30 jan - 2 feb]@row=1)1100,"")+IF(AND([Price Type]@row="SP4",[23rd Total Radiology 30 jan - 2 feb]@row=1)1300,"")+IF(AND([Price Type]@row="FOC",[23rd Total Radiology 30 jan - 2 feb]@row=1),0,"")

Once each IFs are evaluated, we get this:

=""+1500+""+""+""+"" ==> _1500__ where "_" is ""

This is not a number for Smartsheet.

I do think now that my solution with a reference table will solve the issue

Paul Newcome

Did you try the SUMIFS to only pull in numbers?

=SUMIFS(range, range, ISNUMBER(@cell)

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.