How to count without duplicates in Excel?-CodePudding

I am trying to count the "iso3" items (in column A) with different combinations without duplicates. For example, "RUS" with different "cnum" (column B) and "country"(column C) have 2 combinations, including "RUS -- 643 --- Russian Federation"(rows 2 and 8) and "RUS -- 842 --- United States of America" (row 13). If I use the formula =COUNTIF($A$2:$A$13,A2),I will get the output in the blue frame, which are counts with duplicates and are not what I want.

As you can see in the following image, the output in the red frame is what I want, which are counts without duplicates.

My formulas in column D are as follows.

A2 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A2&B2&C2)
A3 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A3&B3&C3)
A4 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A4&B4&C4)
A5 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A5&B5&C5)
A6 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A6&B6&C6)
A7 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A7&B7&C7)
A8 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A8&B8&C8)
A9 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A9&B9&C9)
A10 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A10&B10&C10)
A11 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A11&B11&C11)
A12 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A12&B12&C12)
A13 = UNIQUE($A$2:$A$13&$B$2:$B$13&$C$2:$C$13,A13&B13&C13)

And I got the error as follows.

A2 = #NAME?
A3 = #NAME?
A4 = #NAME?
A5 = #NAME?
A6 = #NAME?
A7 = #NAME?
A8 = #NAME?
A9 = #NAME?
A10 = #NAME?
A11 = #NAME?
A12 = #NAME?
A13 = #NAME?

Please help me to find what causes this error. Thank you.

CodePudding user response：

This formula should work for you, in cell D2 and copied down:

=SUMPRODUCT(--($A$2:$A$13=A2),1/COUNTIFS($A$2:$A$13,$A$2:$A$13,$B$2:$B$13,$B$2:$B$13,$C$2:$C$13,$C$2:$C$13))