Count Unique Values with Multiple Countif Conditions

[ssuddendorf]

Hello All,

I could use some help figuring out how to create a formula that will calculate the number of unique values where there are multiple countif conditions.

This is a sample of the kind of data I'm working with.

I need to find the count of unique child ID's for each Parent that have certain colors.

For example Parent 20095 has 1 unique child, but parent 20129 has 3.  I can figure out how to do the count of colors for each parent but not how to layer in the count of unique child IDs.

=COUNTIFS(D2:D22,D11,E2:E22,"blue")+COUNTIFS(D2:D22,D11,E2:E22,"Red")

Any help would be greatly appreciated.

[#2 · Accepted solution]

@ssuddendorf 

=COUNT(UNIQUE(FILTER($C$2:$C$22,($D$2:$D$22=G12)*(($E$2:$E$22=H12)+($E$2:$E$22=I12)))))

With Office 365 you can apply UNIQUE and FILTER.