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?

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

markkrebs

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

markkrebs

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

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

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

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.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.