SUMIFS Formula Help

JL_LW

Hi there,

Super stuck on how to get the below working, any help/advice about where I am going wrong would be greatly appreciated.

Smartsheet 1 - Data Sheet, where:

Range 1 = Dropdown list column

Range 2 = £ value

Range 7 = Contact Column

Smartsheet 2 - Summary Sheet, where I am building formula (hence the References above)

What I am looking to achieve is to sum the total Range 2 (£ Value), IF the following conditions are met:

Range 1 = "<>" (not blank)
Range 7 = Name or Email

I thought I was right with something along the following but have now tried so many options and I can't get it working I am hoping someone might be able to point me in the right direction.

=SUMIFS({Range 2}, {Range 1}, "<>", [{Range 7}], "HAS(@cell, "Name"), [{Range 7}], "HAS(@cell, "Email"))

Many thanks in advance for your suggestions!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Try this instead:

=SUMIFS({Range 2}, {Range 1}, @cell <> "", {Range 7}, OR(HAS(@cell, "Name"), HAS(@cell, "Email")))

Is your dropdown column a multi-select or single select?

JL_LW

Thanks for your comment Paul. Unfortunately, that formula also has not worked. It shows #Incorrect Argument Set

The dropdown is a single select, value-restricted column.

JL_LW

I have also tried:

=SUMIFS({Range 2}, {Range 1}, @cell "<>", {2022-23 Events and Conferences List Range 7}, OR(@cell = "Name", @cell = "Email"))

And

=SUMIFS({2022-23 Events and Conferences List Range 2}, {2022-23 Events and Conferences List Range 1}, @cell "<>", AND({2022-23 Events and Conferences List Range 7}, OR(HAS(@cell = "Name"), HAS(@cell = "Email"))))

Paul Newcome

If it is single select then we shouldn't need the HAS function. That should help clean things up a little bit and give us one less factor to trouble shoot.

=SUMIFS({Range 2}, {Range 1}, @cell <> "", {Range 7}, OR(@cell = "Name"), @cell = "Email"))

JL_LW

Thanks Paul.

I have circumnavigated some of the issues for one of my formula sets (made it less complicated) by swapping the contact list to a new column which =1 if the contact is 'X'.

This has helped me total the Expected costs for each team member.

However I am still having the issue with calculating the following SUMIFS:

Smartsheet #1 - Data Sheet, where:

The Range is (Range 2) = £ value (to sum)

Criterion 1 = (Range 1) = "Attended" (single select dropdown list column)

Criterion 2 = (Range 8) =1 (Where 1 represents the person ('X@) mentioned above)

I am building this in Smartsheet #2 - Summary Sheet

I keep getting #Unparseable

=SUMIFS({Range 2}, {Range 1}, "Attended", {Range 8}, =1)

Any ideas?

Paul Newcome

Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?

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.