Counting the number of different entries in a column

  • Thread starter Thread starter Wombat
  • Start date Start date
W

Wombat

Right!

column A contains area codes (two digit numbers, eg. 30), column B customer
numbers (eg 999999)

The lists then go down listing orders. I want to count how many different
customers have placed an order per area (not counting duplicates)

Thanks for any help
 
With the query area code in cell C1 enter the below formula. Please note that
this is an array formula. You create array formulas in the same way that you
create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

=SUM(IF(FREQUENCY(IF(A1:A100=C1,MATCH(B1:B100,B1:B100,0)),
ROW(B1:B100)-ROW(A1)+1),1))
 
You may also check [I did not] the Array-Formula:
{=COUNT(1/FREQUENCY(IF($A$1:$A$100=C1,$B$1:$B$100),$B$1:$B$100))}
Micky
 
Back
Top