Menu
In column A I have list of different names. In column B, I have values either 0 or 1.
I want to get a count of all the unique names from column A which have 1 in column B.
Using below array formula I am able count unique names but not able to apply condition on column B.
=SUM(1/COUNTIF(A:A,A:A)) 
533
Using SUM, IF, and COUNTIF Functions. In Excel, functions are always available to solve any operations. In this case, you can use a combination of SUM, IF and COUNTIF functions to count unique values in Excel. To count unique values, enter the formula =SUM(IF(COUNTIF(range, range)=1,1,0)) in the desired cell.
Assuming no more than 100 rows try this "array formula" to count the different names in A2:A100 where there is a 1 in the same row in B2:B100:
=SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))
confirmed with CTRL+SHIFT+ENTER
Note that I say different not unique as the two are not the same
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
