COUNTIFS Formula

Good day,

I am trying to write a formula that combines 2 sections for a dashboard. I want to do Assigned to + % Completed. If I do a countif formula that references assigned to only, data populates accurately. But if I do the below formula, I get #INCORRECT ARGUMENT SET.

Whats weird is that this exact same formula on a different metric sheet works as intended, but it won't work now. Can you tell from the images/below formula what I am doing incorrectly?

=COUNTIFS({Weston Team - Tasks | Assigned to}, $[Primary Column]@row, {Weston Team - Tasks | % COMPLETE}, [Column2]$85)

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

I don't see an issue with your formula, so I suspect the problem may be with one of your references. Since you mention you are able to lookup the Assigned to column correctly, we can likely assume it is okay. Maybe check the {Weston Team - Tasks | % COMPLETE} reference to see if you set it up but did not specify the column. Doing this would result in that specific error.

WesMcNally

So I think it has something to do with the range for "Assigned to". Creating formulas with multiselect as an option such as the assigned to column having 5 names on it, became very difficult.

What I did was created 5 columns. Assigned to 1 - Assigned to 5. And you can see in the screenshot, for the reference, I highlighted those 5 columns.

When I create the formula but this time only use Assigned to 1 instead of highlighting all 5 columns, its working as intended. The formula below is the one that is working but its Onlys referencing Range 2 which is just Assigned to 1. Because I am highlighting 5 assigned to columns, does something with the formula have to change?

Formula w/ 1 assigned to column highlighted:

=COUNTIFS({Weston Team - Tasks Range 2}, [Primary Column]@row, {Weston Team - Tasks | % COMPLETE}, [Column2]86)

Formula w/ 5 assigned to columns highlighted (Doesn't work):

=COUNTIFS({Weston Team - Tasks | Assigned to}, [Primary Column]@row, {Weston Team - Tasks | % COMPLETE}, [Column2]86)

WesMcNally

https://community.smartsheet.com/discussion/comment/397444#Comment_397444

@Carson Penticuff @ing you incase it didn't notify you of my response.

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