Formula to count unique columns

Hello,

I need to do a counting of how many Calls have a certain Part Number. The hard part is: on the table, there are some Calls that have repeated PNs (part numbers) and I don't want to count those. (The table contain over 2.000 rows)

For example:

Call	Part Number
1050	0S1571
1050	0T2080
1030	0S1571
1030	0T2080
1050	0S1571

I need the following result:

0S1571: 2
0T2080: 2

One possible way:
=SUM(INDEX(($B$2:$B$6=$F1)/COUNTIFS($A$2:$A$6,$A$2:$A$6,$B$2:$B$6,$B$2:$B$6),0))

See sheet.

You're welcome & thanks for the rep.

Countifs counts the number of cells in a range that meet mutliple criteria. I'll give an example from a course I had since the book is in front of me.

A B C D
Daniels Jackson elementary XXX
Wanda Hillsdale high school XXXX
Lewis Jackson high school XXXXX
Williams Jackson high school XX
Shreev Acorn Intermediate XX


=COUNTIFS(C2:C13, "high school", B2:B13, "Jackson")

The formula goes like this =Countifs(criteria_range1, criteria, criteria_range2, criteria2, ...) all the way up to 127

This basically counts all the "high school" criterion in C2:C13 and matches it to the criterion in B2:13 which is Jackson, thus the result of it would be 2.


If I understand that formula correctly, it's counting A2-A6 with the matching criterion and doing the same for B2-B6 and outputting a number (within the sum function).

Try this instead. However, you may need VBA, as you are expecting Excel to do rather a lot!!!

=SUM(--(FREQUENCY(IF(B$2:B$6=F1,MATCH($A$2:$A$6,$A$2:$A$6,0)),ROW($A$2:$A$6)-ROW($B$2)+1)>0))

This is an array formula.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...