Distinct CountIFS Statement

I need a little assistance...

I was able to get the formula to partially work using =count(distinct but, it was not taking the name cell into account and returned the same number in each row.

I need a distinct count of PrAbv based on P Name.

I got "#inccorect argument" when I changed count to countifs. I'm sure it's something simple I'm missing here.

=COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "B")) + COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "U")) + COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "A")) + COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "C")) + COUNTIFS(DISTINCT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "P"))

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

In that case you would need something more like

=COUNT(DISTINCT(COLLECT({Plan}, {Name}, @cell = "x")))

All comments

Paul Newcome

You need to use a COUNT/DISTINCT/COLLECT.

nhornCGT

@Paul Newcome

I'm still getting a total in the sheet versus what is actual for the Name.

=COUNT(DISTINCT(COLLECT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "B")))+rest

Returns 5 when it should return 4.

Now, I did notice if I use the statement without adding the additional statements, it returns 1 which is correct.

Paul Newcome

what exactly do you mean by the "+rest" portion above?

nhornCGT

@Paul Newcome

Sorry, Paul for the late response. Just getting back to this project.

+rest was adding the addition statements for "U, A, C, and P". Essentially, duplicating the statement. =COUNT(DISTINCT(COLLECT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "B"),+ COUNT(DISTINCT(COLLECT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "C"),+ COUNT(DISTINCT(COLLECT({Full_Name}, @cell = [P Name]@row, {PrAbv}, @cell = "U") etc...

I need a distinct count to remove the duplicated items.

Paul Newcome

I'm still not sure I follow what you are trying to accomplish. Are you wanting a count of distinct entries in the Plan column based on the Name column being equal to "x"?

nhornCGT

@Paul Newcome

Correct.

x = 4

y=2

z= 5

total count 11

Paul Newcome

In that case you would need something more like

=COUNT(DISTINCT(COLLECT({Plan}, {Name}, @cell = "x")))

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.