Countifs with Unique values

Crystal Panning

Hi, 

I have a sheet within parent/child rows, and need to count the number of unique contacts within each of the children rows of each parent. I have a vendor column that has a parent row and children row (only the children rows list the vendor contacts). 

I need to count the number of unique vendor contacts, within a vendor, and if the vendor status is green. 

I tried the Countifs formulas, but need to figure out how to capture the unique names only for that vendor. Any suggestions on what this formula would be?

This is what i had, but shows as unparseable. 

=IF(COUNTIFS([Vendor Name]:[Vendor Name], Vendor1, [Status]:[Status], Status1, [Candidate Name]$1:[Candidate Name]1, [Candidate Name]1) <> 1, 0, 1)

 

Thank you,

Crystal

Paul Newcome

The easiest way to go about this would be to combine some kind of unique data from the parent row with the data from each of the child rows.

So if you have a column that has a unique identifier (such as a candidate name that isn't duplicated within that column on that sheet), you would use a new "Helper Text" column with something like this in it...

 

=PARENT([Candidate Name]@row) + [Vendor Name]@row

.

What this would do is give you a text string that contains candidate and vendor within the same cell.

.

You would then add in another "Helper Checkbox". In row 1 of this column you would enter the following and dragfill on down:

 

=IF(COUNTIFS([Helper Text]$1:[Helper Text]@row, [Helper Text]@row) = 1, 1)

.

This will check the box for the first occurrence of the Candidate/Vendor name appears in the first "Helper Text" column.

 

You would then use something like this in the parent rows of your column of choice (other than the "Helper Checkbox" column):

 

=COUNTIFS(CHILDREN([Helper Checkbox]@row), 1, CHILDREN(Status@row), "Green")

Crystal Panning

Thank you for answering this. In regards to the first step, when I did the formula, the children still carried the same identifier as the parent, even though the helper text I used was different. Do you know what adjustments I should make? 

 

Paul Newcome

My apologies. I'm not sure I follow exactly what you're asking. Are you able to provide some screenshots with sensitive/confidential information removed, blocked, or replaced with "dummy data"?