SUMPRODUCT & COUNTIFS

[Tripp]

Hello,

I have been playing around with counting unique values in a column based on other values in another column using a combination of COUNTIFS and SUMPRODUCT. After lots of experimenting, I then searched and found the following formula but I'm not entirely sure how it works. Could someone explain why the first array divided by the COUNTIFS works?

=IF(SUMPRODUCT(--(Tbl_1[Col1]="A")/(COUNTIFS(Tbl_1[Col1],Tbl_1[Col1]&"",Tbl_1[Col2],Tbl_1[Col2]&"")))<>Total No,"Check List","List OK")

I understand that the "" removes any blanks and the first array shows all the values that match the first criteria. I also get that the COUNTIFS is showing me the unique values but I don't see why the division works.

I hope this is clear enough, but let me know if not.

TIA
Tripp

 

[bosco_yip]

Try to check this link of which may help you to understand the logic of 1/COUNTIF(…....) in the Count unique value formula

https://www.get-digital-help.com/2009/06/09/count-unique-values-in-a-column-in-excel/

Regards
Bosco

 

[Tripp]

Thank's Bosco, that's very clear.