Counting duplicate rows once under parent rows

ASanders

I am trying to figure out how to count rows in a column that have duplicate entries only once. In my screen shot below, you can see under the "Action Area" column that a "Resident" can have multiple actions under the same action area. Here' it's "diet." But for each "Resident", I want to be able to count "diet" only once for each "Resident" in the parent row (not 5 times as with the first resident or 4 times as with the second resident). Is that possible? TIA!

Paul Newcome

Where exactly are you wanting to put the formula, and what would be the expected output for each of those two sections in your screenshot?

ASanders

I'd want to use the formula to create a metric/report.

What I want to be able to say is "Residents had a total of 25 goals that included 40 action areas." Across residents the action areas may be duplicated (i.e. multiple residents may have actions under the "diet" action area), but within a resident I'd only want to count the action area once.

I see that I could manipulate it by only tagging the action area label once under each resident (as I did with "Goal Area"), but for other purposes I'd like to be able to run the action area label down the column for each corresponding action listed under the "Resident" column. So am trying to figure out if there's a way to count it only once within each Resident grouping.

Paul Newcome

And where would the formula reside?

ASanders

@Paul Newcome The formula would reside in a separate sheet where I'm analyzing metrics from this sheet.

Paul Newcome

In that case you would use

=COUNT(DISTINCT({range}))

ASanders

@Paul Newcome Unfortunately, I think, that counts an occurrence only once in the entire column. What I want to do is count once within each parent cluster (i.e, each group underneath a darker blue row in my screen shot). So, in my screen shot example, "diet" would get counted twice, not once.

Paul Newcome

In that case you would need a helper column that outputs the Action Area along with data from a cell in the parent row in a single string. Then you would count the distinct entries in the helper column.

=PARENT(Resident@row) + [Action Area]@row