Stacked bar chart formula using COUNTIFS and HAS functions

I have created a helper sheet to use for building a stacked bar chart. The sheet data should count how many open tasks an individual is assigned to from two different sheets. Because there may be multiple contacts in an Assigned To column, I need to update this formula by adding HAS. I cannot get it to work correctly in this helper sheet.

My formula:

=COUNTIFS({Network General Operations Tasks Assigned To}, HAS($Person@row, {Network General Operations Tasks Status}, <>"Complete"))

The formula is delivering "0" as a result for Brian, when it should be 13 based on the sheet summary formula from the source sheet (Network General Operations Tasks):

Any ideas on what I'm missing here? Appreciate it!

Find more posts tagged with

Formulas

Accepted answers

KPH

Ah yes, one other thing missing - the range for the HAS function.

=COUNTIFS({Network General Operations Tasks Assigned To}, HAS(@cell,$Person@row), {Network General Operations Tasks Status}, <>"Complete")

All comments

KPH

Hi@Laura L

It looks like the closing parenthesis for your HAS is in the wrong place. Try moving it to the end of the first criteria (in bold here), instead of at the end of the formula.

=COUNTIFS({Network General Operations Tasks Assigned To}, HAS($Person@row), {Network General Operations Tasks Status}, <>"Complete")

Also FYI - your screen shot above shows Brian's full name in the formula. I think you can edit the post.

Laura L

Thanks for your quick reply! I made the change you suggested and now I am getting an Incorrect Argument Set error.

KPH

Ah yes, one other thing missing - the range for the HAS function.

=COUNTIFS({Network General Operations Tasks Assigned To}, HAS(@cell,$Person@row), {Network General Operations Tasks Status}, <>"Complete")

Laura L

This works beautifully, thank you so much!

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.