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:

1. Range 1 = "<>" (not blank)
2. 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!

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?