Count Unique values based on multiple criteria ignoring blanks

[baleshst]

<!-- Please do not remove this header -->

<tbody>
[TD="bgcolor: #FFFFFF"] Name [/TD]
[TD="bgcolor: #FFFFFF"] Zone [/TD]
[TD="bgcolor: #FFFFFF"] State [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF"] Balesh [/TD]
[TD="bgcolor: #FFFFFF"] East [/TD]
[TD="bgcolor: #FFFFFF"] MH [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF"] James [/TD]
[TD="bgcolor: #FFFFFF"] West [/TD]
[TD="bgcolor: #FFFFFF"] BH [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF, align: right"] [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF"] Kelly [/TD]
[TD="bgcolor: #FFFFFF"] East [/TD]
[TD="bgcolor: #FFFFFF"] MH [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF"] James [/TD]
[TD="bgcolor: #FFFFFF"] West [/TD]
[TD="bgcolor: #FFFFFF"] BH [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF"] Kelly [/TD]
[TD="bgcolor: #FFFFFF"] West [/TD]
[TD="bgcolor: #FFFFFF"] BH [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF"] Balesh [/TD]
[TD="bgcolor: #FFFFFF"] East [/TD]
[TD="bgcolor: #FFFFFF"] MH [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF, align: right"] [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]

[TD="bgcolor: #FFFFFF"] Criteria to count unique values [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]
[TD="bgcolor: #FFFFFF, align: right"] [/TD]
[TD="bgcolor: #FFFFFF"] Unique Count [/TD]

[TD="bgcolor: #FFFFFF"] James [/TD]
[TD="bgcolor: #FFFFFF"] West [/TD]
[TD="bgcolor: #FFFFFF"] BH [/TD]
[TD="bgcolor: #FFFF00, align: right"] [/TD]

</tbody>

Please help in provide unique count of column A based on the criterias of Name, Zone and State as show above.

Please note in the above tabel the count of unique values based on the criteria should be 1, There is also a blank row in between. Formula should be able to ignore this blank row.

 

[DILIPandey]

Hi baleshst,

Try below formula

=IF(SUMPRODUCT(($A$1:$A$8=$E$3)*($B$1:$B$8=$F$3)*($C$1:$C$8=$G$3))>0,1,0)

where E3, F3 and G3 are references to criteria given by you.


Regards,
DILIPandey