Cross Sheet Distinct Count Formula

Whelandon

I'm trying to count distinct values in a different sheet using a cross sheet formula. More specifically, I have parent/child rows, and I only want to count the parent rows. The child rows have duplicate values in the column that I'm counting, which makes me think that something like countif(distinct(something...) will work. Or am I better off using something along the lines of Countif(Descendants=0) or something like that?

Thanks in advance.

Paul Newcome

To start... Hierarchy functions do not work with cross sheet formulas. You are going to want to put a column on your source sheet that somehow denotes which rows you want to count vs which you wan to exclude. From there the COUNT(DISTINCT(COLLECT( would be the way to go.

Paul Newcome

To start... Hierarchy functions do not work with cross sheet formulas. You are going to want to put a column on your source sheet that somehow denotes which rows you want to count vs which you wan to exclude. From there the COUNT(DISTINCT(COLLECT( would be the way to go.

Mommaduck

Also trying to get a distinct count formula to work. Sheet data is fed by forms submitted by multiple people. Combined name column (that I want to point to) is generated by a formula joining first and last name. We report distinct visits on a monthly report so need the info to be "clean". Need a quick way for the group who's data this is to check their entries for misspellings (so I don't crazy when doing my report!) I know I could sort the sheet by name column but I find that visually it's still difficult to see the misspellings. Names to be entered are varied and not a set list so can't do a drop down menu. Trying to avoid exporting to Excel to do this.

This is what I wrote =COUNT(DISTINCT({Living Room Intake Sheet FY2020 Combined name})) and I got a number - I need the names.

Suggestions??

Paul Newcome

@Mommaduck Try a JOIN/DISTINCT.