Count unique with multiple criteria
 

Hello,

I am trying to count the number of times an item occurs with multiple
requirements. I have the overall count of all by name and with other
multiple countif requirements, but now I need to find the count of the number
of unique items by ID and unique IDs with other criteria as well. Some of
the column ID's may not have numbers in them, but I would still like them
counted. For instance, see below.

Overall
Total number of companies for North - 4
Total number of unique companies for North - 2
With additional criteria
Total number of companies in West and Small Market - 3
Total number of unique companies in West and Small Market - 1(does not have
a unique ID to identify them and name may be slightly different))

Name Region Market Manager ID
ABC- North Large Bob 123
DEF East Med Joe 345
GHI South Med Jane 234
JKL West Small Joe
MNO West Med Jane 686
PQR North Small Jane 897
STU West Med Joe 976
VWX East Large Bob
YZ South Med Jane 967
ABC Inc North Large Bob 123
ABC North Large Bob 123
JKL West Small Joe
JKL Inc West Small Joe

Any help would be appreciated! Thanks!

Count unique with multiple criteria
 

Jo wrote:
Hello,

I am trying to count the number of times an item occurs with multiple
requirements. I have the overall count of all by name and with other
multiple countif requirements, but now I need to find the count of the number
of unique items by ID and unique IDs with other criteria as well. Some of
the column ID's may not have numbers in them, but I would still like them
counted. For instance, see below.

Overall
Total number of companies for North - 4
Total number of unique companies for North - 2
With additional criteria
Total number of companies in West and Small Market - 3
Total number of unique companies in West and Small Market - 1(does not have
a unique ID to identify them and name may be slightly different))

Name Region Market Manager ID
ABC- North Large Bob 123
DEF East Med Joe 345
GHI South Med Jane 234
JKL West Small Joe
MNO West Med Jane 686
PQR North Small Jane 897
STU West Med Joe 976
VWX East Large Bob
YZ South Med Jane 967
ABC Inc North Large Bob 123
ABC North Large Bob 123
JKL West Small Joe
JKL Inc West Small Joe

Any help would be appreciated! Thanks!

Right, the trick is all in your last sentence: the "name may be slightly
different".

Question for you:

How would you, using logic, discern whether these "company names" refer
to the same or different entity?

ABC-
ABC Inc
ABC
ABC 123 I learned it on Sesame Street
JKL
JKL oo looks like a match
JKL Inc

Since this is obviously all bogus data and we can't do something trivial
like "look at the left 3 characters", you will need to think about or
speak more to the actual situation at hand.

Count unique with multiple criteria
 

The ID column is what discerns if these companies belong to the same group or
family.

"smartin" wrote:

Jo wrote:
Hello,

I am trying to count the number of times an item occurs with multiple
requirements. I have the overall count of all by name and with other
multiple countif requirements, but now I need to find the count of the number
of unique items by ID and unique IDs with other criteria as well. Some of
the column ID's may not have numbers in them, but I would still like them
counted. For instance, see below.

Overall
Total number of companies for North - 4
Total number of unique companies for North - 2
With additional criteria
Total number of companies in West and Small Market - 3
Total number of unique companies in West and Small Market - 1(does not have
a unique ID to identify them and name may be slightly different))

Name Region Market Manager ID
ABC- North Large Bob 123
DEF East Med Joe 345
GHI South Med Jane 234
JKL West Small Joe
MNO West Med Jane 686
PQR North Small Jane 897
STU West Med Joe 976
VWX East Large Bob
YZ South Med Jane 967
ABC Inc North Large Bob 123
ABC North Large Bob 123
JKL West Small Joe
JKL Inc West Small Joe

Any help would be appreciated! Thanks!

Right, the trick is all in your last sentence: the "name may be slightly
different".

Question for you:

How would you, using logic, discern whether these "company names" refer
to the same or different entity?

ABC-
ABC Inc
ABC
ABC 123 I learned it on Sesame Street
JKL
JKL oo looks like a match
JKL Inc

Since this is obviously all bogus data and we can't do something trivial
like "look at the left 3 characters", you will need to think about or
speak more to the actual situation at hand.
.

Count unique with multiple criteria
 

You would discern if the company names refer to the same or different entity
based on the ID. The ID is the family or group ID, so they will be constant,
whether or not the name differs.

"smartin" wrote:

Jo wrote:
Hello,

I am trying to count the number of times an item occurs with multiple
requirements. I have the overall count of all by name and with other
multiple countif requirements, but now I need to find the count of the number
of unique items by ID and unique IDs with other criteria as well. Some of
the column ID's may not have numbers in them, but I would still like them
counted. For instance, see below.

Overall
Total number of companies for North - 4
Total number of unique companies for North - 2
With additional criteria
Total number of companies in West and Small Market - 3
Total number of unique companies in West and Small Market - 1(does not have
a unique ID to identify them and name may be slightly different))

Name Region Market Manager ID
ABC- North Large Bob 123
DEF East Med Joe 345
GHI South Med Jane 234
JKL West Small Joe
MNO West Med Jane 686
PQR North Small Jane 897
STU West Med Joe 976
VWX East Large Bob
YZ South Med Jane 967
ABC Inc North Large Bob 123
ABC North Large Bob 123
JKL West Small Joe
JKL Inc West Small Joe

Any help would be appreciated! Thanks!

Right, the trick is all in your last sentence: the "name may be slightly
different".

Question for you:

How would you, using logic, discern whether these "company names" refer
to the same or different entity?

ABC-
ABC Inc
ABC
ABC 123 I learned it on Sesame Street
JKL
JKL oo looks like a match
JKL Inc

Since this is obviously all bogus data and we can't do something trivial
like "look at the left 3 characters", you will need to think about or
speak more to the actual situation at hand.
.

Count unique with multiple criteria
 

Hi,

If desired, kindly send the file to me at ask(at)ashishmathur(dot)com.
Please explaint he problem very clearly. Thank you.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jo" wrote in message
...
Hello,

I am trying to count the number of times an item occurs with multiple
requirements. I have the overall count of all by name and with other
multiple countif requirements, but now I need to find the count of the
number
of unique items by ID and unique IDs with other criteria as well. Some
of
the column ID's may not have numbers in them, but I would still like them
counted. For instance, see below.

Overall
Total number of companies for North - 4
Total number of unique companies for North - 2
With additional criteria
Total number of companies in West and Small Market - 3
Total number of unique companies in West and Small Market - 1(does not
have
a unique ID to identify them and name may be slightly different))

Name Region Market Manager ID
ABC- North Large Bob 123
DEF East Med Joe 345
GHI South Med Jane 234
JKL West Small Joe
MNO West Med Jane 686
PQR North Small Jane 897
STU West Med Joe 976
VWX East Large Bob
YZ South Med Jane 967
ABC Inc North Large Bob 123
ABC North Large Bob 123
JKL West Small Joe
JKL Inc West Small Joe

Any help would be appreciated! Thanks!