Forum Discussion
How to count not empty cells and avoiding duplicates
Hi, I have a problem to count how many different cells per person in a list, either I can count avoiding the duplicates but empty cells are a problem, or count avoiding empty cells and duplicates are...
=SUM(N(IF(($B$2:$B$14=F2)*NOT(ISBLANK($C$2:$C$14)),MATCH(IF(($B$2:$B$14=F2)*NOT(ISBLANK($C$2:$C$14)),$C$2:$C$14),IF(($B$2:$B$14=F2)*NOT(ISBLANK($C$2:$C$14)),$C$2:$C$14),)=ROW($1:$13))))
An alternative could be this formula if you work with e.g. Excel 2013. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.