Get the counting of something to get unique info

[Guest]

Hello,

Is it possible to obtain the number of unique occurences by some ways if I
have the information as follows on three columns:

No City Postal Code
1234 BRAMPTON GGG
3412 TORONTO HHH
3412 TORONTO HHH
1452 YORK UUU
5623 LONDON YYY

I would like to have two results that looks like this:
Number of suppliers for Postal Code HHH = 1 (not 2)
Number of suppliers = 4 (not 5)

Thanks,
gmore

 

[Bob Phillips]

Count all uniques

=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))

All unique HHH

=COUNT(1/FREQUENCY(IF(C2:C20="HHH",IF(C2:C20<>"",A2:A20)),IF(C2:C20="HHH",IF(A2:A20<>"",A2:A20))))

which is an array formula, committed with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob,

I forgot to mention that it is possible to have more than one city for a
given supplier. So, as example, 3412 should be counted as two entities in
this example:

No City Postal Code
1234 BRAMPTON GGG
3412 MONTREAL JJJ
3412 TORONTO HHH
3412 TORONTO HHH
1782 MISSISSAUGA HHH
1452 YORK UUU
5623 LONDON YYY

How would do you reajust your formulas for both results?
I would like to have two results that looks like this:
1. Number of suppliers with Postal Code HHH = 2 (not 3)
2. Number of suppliers = 6 (not 7)

Thanks, I appreciate your help!
gmore

 

[Guest]

Try something like this:

With
Your posted data structure in A1:a10

And
D1: (the postal code to match)

Then
This regular formula returns the count of unique "No" and "City"
combinations with that Postal Code:
E1:
=SUMPRODUCT(--(FREQUENCY((C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0),(C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0))*(C1:C11=D1)>0))

and this regular formula returns the count of unique "No" and "City"
combinations
F1:
=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0),MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0))*(ROW(A1:A10)>1)>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks! That's really cool!

Try something like this:

With
Your posted data structure in A1:a10

And
D1: (the postal code to match)

Then
This regular formula returns the count of unique "No" and "City"
combinations with that Postal Code:
E1:
=SUMPRODUCT(--(FREQUENCY((C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0),(C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0))*(C1:C11=D1)>0))

and this regular formula returns the count of unique "No" and "City"
combinations
F1:
=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0),MATCH(A2:A10&B2:B10&"_",A2:A10&B2:B10&"_",0))*(ROW(A1:A10)>1)>0))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[KL]

=SUMPRODUCT(--(FREQUENCY((C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0),(C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0))*(C1:C11=D1)>0))

Hi Ron,

Why not just:

=SUMPRODUCT(--(FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0),MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0))*(C1:C11=D1)>0))

Regards,

KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[Guest]

Thank you so much for the feedback. It's much appreciated.....I'm just glad I
could help.

***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks, KL

I was experimenting with various numbers/text/blanks scenarios and I didn't
fine tune my formula enough after I got it to work. Thanks for jumping in and
cleaning it up.

***********
Regards,
Ron

XL2002, WinXP

=SUMPRODUCT(--(FREQUENCY((C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0),(C1:C10=D1)*MATCH(A1:A10&B1:B10&"_",A1:A10&B1:B10&"_",0))*(C1:C11=D1)>0))

Hi Ron,

Why not just:

=SUMPRODUCT(--(FREQUENCY(MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0),MATCH(A1:A10&B1:B10,A1:A10&B1:B10,0))*(C1:C11=D1)>0))

Regards,

KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[Domenic]

For the number of unique suppliers/city with postal code 'HHH', try the
following formula which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(C2:C8="HHH",MATCH(A2:A8&B2:B8,A2:A8&B2:B8,0)),ROW(C2
:C8)-ROW(C2)+1),1))

To exclude rows where Column C equals HHH and the corresponding values
in Column A and B are blank, try the following instead...

=SUM(IF(FREQUENCY(IF(C2:C8="HHH",IF(A2:A8&B2:B8<>"",MATCH(A2:A8&B2:B8,A2:
A8&B2:B8,0))),ROW(C2:C8)-ROW(C2)+1),1))

Hope this helps!