SUMIFS Formula Help

edited 10/28/22 in Formulas and Functions

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
    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"))


    =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
    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
    Paul Newcome ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!