SUM IF / COLLECT / AND all are failing

I have tried several different ways to do what I thought should be a simple calculation. The purpose is to add Hours from one column where the Priority in a second column is either 1, 2, or 3.

For perspective there are 300+ rows in the sheet and it grows daily. There are 8 possible priority settings, one is text. I have tried using a helper column and converting the text to 0. The Priority column is also dropdown, which I thought might also be causing a problem.

=SUMIFS([ETC Marmon]:[ETC Marmon], Priority:Priority, 1, Priority:Priority, 2, Priority:Priority, 3)

The above produces 0

=SUMIFS([ETC Marmon]:[ETC Marmon], Priority1:Priority1, OR(@cell = "1", @cell = "2", @cell "3"))

The above produces and #Incorrect Argument Set error

I have also tried writing the first one with "" around the numbers and using the helper column.

I have tried writing individual SUMIFs inside of a SUM statement.

What am I missing? Can anyone see what I can't see at this point?

Answers

  • markkrebs
    markkrebs Community Champion

    question: There are 8 possible priority settings, one is text Are you saying the text "one" is in the cell?

  • markkrebs
    markkrebs Community Champion
    edited 01/23/25

    if it's numeric then use this. you dont need the " "

    =SUMIFS(Hours:Hours, Priority:Priority, OR(@cell = 1, @cell = 2, @cell = 3 ))

  • LDLValentine
    LDLValentine ✭✭✭✭✭

    Good morning -

    Tried that, ETC Marmon is Hours and is numeric and is a Text/Number field

    Priority1 is the Helper field and is numeric only and is a Text/Number field

    =SUMIFS([ETC Marmon]:[ETC Marmon], Priority1:Priority1, OR(@cell = 1, @cell = 2, @cell = 3 ))

    I am still getting the same "Incorrect Argument Set" error

    To answer the previous question, in the Priority field on of the Dropdown selections is "Needs Priority", the others are 1, 2, 3, 4, 5, 10, 99. Each of the numbers have different designations. For the purpose of this exercise, I only want to capture the hours for the top 3 priorities.

  • LDLValentine
    LDLValentine ✭✭✭✭✭

    Hello Mark - your answer got me 99% there. Fresh eyes got me the rest of the way there this morning. For whatever reason, placing [ ] around Priority1 gave me the answer I needed.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!