Duplicate counting adjacent cells

**Wizard33** · 03-15-2010, 06:08 PM

Hello all,
My issue this time around is this: I would like to find unique values of B2:B12 and then count them if they are 1) unique and 2) have text in the adjacent cells (C2:D2). I would like the count to ignore the row if has already been counted once. In my example there is an "Example" sheet and a "Results" sheet that may convey this idea better. As seen on the "Results" sheet G2=2 because Blue has 2 different uniques numbers in B2:B12 AND has text in that row despite having text in three "Blue" rows.

**DonkeyOte** · 03-16-2010, 08:20 AM

Perhaps you want:

Code:

G2:
=SUM(IF(FREQUENCY(IF(($A$2:$A$12=$F2)*($C$2:$C$12&$D$2:$D$12<>""),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1)>0,1))
confirmed with CTRL + SHIFT + ENTER
copied down

**Wizard33** · 03-16-2010, 10:35 AM

Works exactly how I had imagined. Thank you for your time and quick response!!!!

**Wizard33** · 03-16-2010, 06:15 PM

I would like to add to this another instance that I've come across when using the above posted formula. I would like to count them only if they are unique and display the text "Pass" in E2:E12. So in the attached book on the "Results" tab, I would like it to count Blue only twice because there are only two unique rows (unique is based of A2:A12 and B2:B12) despite having a duplicate filled in E2:E12. I would like to ignore the duplicate and have only a unique system count that Pass.

**DonkeyOte** · 03-16-2010, 06:54 PM

Perhaps then...

Code:

=SUM(IF(FREQUENCY(IF(($A$2:$A$12=$G2)*($C$2:$C$12&$D$2:$D$12<>"")*($E$2:$E$12="Pass"),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1)>0,1))
confirmed with CTRL + SHIFT + ENTER