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
-
question: There are 8 possible priority settings, one is text Are you saying the text "one" is in the cell?
-
if it's numeric then use this. you dont need the " "
=SUMIFS(Hours:Hours, Priority:Priority, OR(@cell = 1, @cell = 2, @cell = 3 ))
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!