Count Unique Items with Multiple Criteria

Andrew,
One way to do it...
1. Sort the data by model and lot number.
2. In an adjacent column join the model and lot values using a formula
similar to: =B6&D6 (assumes data in B5:D11).
3. In another adjacent column enter a formula similar to:
=IF(F6=F5,"",COUNTIF($F$6:$F$11,F6)) (assumes joined values in column F).
4. Fill all formulas down to bottom of data.

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

<rrstudio2@icqmail.com>
wrote in message
I am trying to get a list of how many lots a particular car model is
on. For example, say we have a spreadsheet that looks like:

Model License Lot
Ford xjd-394 1
Chevy gwg-394 2
Ford sdf-333 1
Ford lkj-111 3
Toyota skd-333 4
Toyota shk-584 4

I am loking for a way to get data that says how many unique lots each
car is on, so for example:
Ford: 2
Chevy: 1
Toyota: 1

I was trying to do this with Pivot tables and the count functionality,
but it isn't quite getting me the results I want. I can get the
results with a pivot table if I drag the Model followed by the lot
into the row column, but this just shows the data like:
Ford 1
3
Chevy 2
Toyota 4
So from here I can manually found that the Ford is on two lots, the
chevy on one lot, and the toyota is on one lot, but it would be great
if I could use a pivot table to get the actual number of lots instead
of having to count them. Any ideas?

-Andrew V. Romero

Reply to this Message

=SUM(N(FREQUENCY(IF(A2:A7="Ford",MATCH(C2:C7,C2:C7,0)),MATCH(C2:C7,C2:C7,0))>0))

Adapt the cell references to fit your real data, it needs to be entered with
ctrl + shift & enter

Signature

Regards,

Peo Sjoblom

>I am trying to get a list of how many lots a particular car model is
> on. For example, say we have a spreadsheet that looks like:
[quoted text clipped - 27 lines]
>
> -Andrew V. Romero

Reply to this Message

Hi

On your PT,
drag Model to the Row Area and
Lot to the Data area (selecting Count as opposed to Sum)
and you will get the result you are looking for.

You don't need Lot in the Row area

Signature

Regards
Roger Govier

>I am trying to get a list of how many lots a particular car model is
> on. For example, say we have a spreadsheet that looks like:
[quoted text clipped - 27 lines]
>
> -Andrew V. Romero

Reply to this Message

I couldn't get Roger's solution to work, but this does:

In your Pivot Table, put "Model" in the Row area.
Put "Lot" in both the Column area and the Data area.
Use the function COUNTIF across the columns of data for each model, using
the criteria >0.

> I am trying to get a list of how many lots a particular car model is
> on. For example, say we have a spreadsheet that looks like:
[quoted text clipped - 27 lines]
>
> -Andrew V. Romero

Reply to this Message

rrstudio2@icqmail.com - 13 Oct 2007 15:34 GMT

On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
wrote:

> I couldn't get Roger's solution to work, but this does:
>
[quoted text clipped - 37 lines]
>
> - Show quoted text -

I tried Roger's method first, but that doesn't give the desired
results. It will for each car count how many lots it is on, not how
many unique lots it is on.I also tried cutting and pasting Peo's
formula, but excel says it contains an error. Jim had a good idea,
but I neglected to say that I can't resort the real data...well I
guess I could but I would have to first number the lines so I could
get it make to its original form.

Traveller, could you provide some more details? When I put it into a
Pivot table, it shows as
Ford 1 (would the countif formula go in column C, countif(a2:a:
4,b2>0)????? That doesn't seem to work.
3
Chevy 2
Toyota 4

If it was a perfect world, I would like the data to be added into a
column on the original data table, for example
Model License Lot UniqueLots
Ford xjd-394 1 2
Chevy gwg-394 2 1
Ford sdf-333 1 2
Ford lkj-111 3 2
Toyota skd-333 4 1
Toyota shk-584 4 1

Thanks for the ideas,
Andrew V. Romero

Reply to this Message

RagDyeR - 13 Oct 2007 15:56 GMT

Peo's formula works *exactly* as you want.

Entered in D1 -
Change the "Ford" to
A2

And don't forget the CSE!

Signature

Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE, drag down to copy to D7.

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
wrote:

> I couldn't get Roger's solution to work, but this does:
>
[quoted text clipped - 37 lines]
>
> - Show quoted text -

I tried Roger's method first, but that doesn't give the desired
results. It will for each car count how many lots it is on, not how
many unique lots it is on.I also tried cutting and pasting Peo's
formula, but excel says it contains an error. Jim had a good idea,
but I neglected to say that I can't resort the real data...well I
guess I could but I would have to first number the lines so I could
get it make to its original form.

Traveller, could you provide some more details? When I put it into a
Pivot table, it shows as
Ford 1 (would the countif formula go in column C, countif(a2:a:
4,b2>0)????? That doesn't seem to work.
3
Chevy 2
Toyota 4

If it was a perfect world, I would like the data to be added into a
column on the original data table, for example
Model License Lot UniqueLots
Ford xjd-394 1 2
Chevy gwg-394 2 1
Ford sdf-333 1 2
Ford lkj-111 3 2
Toyota skd-333 4 1
Toyota shk-584 4 1

Thanks for the ideas,
Andrew V. Romero

Reply to this Message

RagDyeR - 13 Oct 2007 16:01 GMT

Since you're copying down, you'll need the absolute references:

=SUM(N(FREQUENCY(IF($A$2:$A$7=A2,MATCH($C$2:$C$7,$C$2:$C$7,0)),MATCH($C$2:$C$7,$C$2:$C$7,0))>0))

Again, don't forget the CSE!

Signature

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Peo's formula works *exactly* as you want.

Entered in D1 -
Change the "Ford" to
A2

And don't forget the CSE!

Signature

Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE, drag down to copy to D7.

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

<rrstudio2@icqmail.com> wrote in message
news:1192286090.945007.247540@k35g2000prh.googlegroups.com...
On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
wrote:

> I couldn't get Roger's solution to work, but this does:
>
[quoted text clipped - 37 lines]
>
> - Show quoted text -

I tried Roger's method first, but that doesn't give the desired
results. It will for each car count how many lots it is on, not how
many unique lots it is on.I also tried cutting and pasting Peo's
formula, but excel says it contains an error. Jim had a good idea,
but I neglected to say that I can't resort the real data...well I
guess I could but I would have to first number the lines so I could
get it make to its original form.

Traveller, could you provide some more details? When I put it into a
Pivot table, it shows as
Ford 1 (would the countif formula go in column C, countif(a2:a:
4,b2>0)????? That doesn't seem to work.
3
Chevy 2
Toyota 4

If it was a perfect world, I would like the data to be added into a
column on the original data table, for example
Model License Lot UniqueLots
Ford xjd-394 1 2
Chevy gwg-394 2 1
Ford sdf-333 1 2
Ford lkj-111 3 2
Toyota skd-333 4 1
Toyota shk-584 4 1

Thanks for the ideas,
Andrew V. Romero

Reply to this Message

Roger Govier - 13 Oct 2007 16:14 GMT

Apologies, I missed that you wanted unique.

Signature

Regards
Roger Govier

> On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 68 lines]
> Thanks for the ideas,
> Andrew V. Romero

Reply to this Message

rrstudio2@icqmail.com - 13 Oct 2007 22:19 GMT

On Oct 13, 8:14 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk>
wrote:

> Apologies, I missed that you wanted unique.
>
[quoted text clipped - 80 lines]
>
> - Show quoted text -

Thanks, I copied the formula that RagDyeR had (with absolute
references) and it works great! I haven't tried using the frequency
or match formulas before, so I look forward to learning more about
them because right now I have no idea why this formula works. Any
chance you can break it down into understandable bits for me?

Thanks,
Andrew

Reply to this Message

Traveller - 14 Oct 2007 02:47 GMT

For what it's worth, I've sent you a sample file with my solution, in case
you want to go that route. I added the VLOOKUP function to get the results
into the format you want. Hope it helps.

> On Oct 13, 7:07 am, Traveller <Travel...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 68 lines]
> Thanks for the ideas,
> Andrew V. Romero

Reply to this Message

rrstudio2@icqmail.com - 15 Oct 2007 18:27 GMT

On Oct 13, 6:47 pm, Traveller <Travel...@discussions.microsoft.com>
wrote:

> For what it's worth, I've sent you a sample file with my solution, in case
> you want to go that route. I added the VLOOKUP function to get the results
[quoted text clipped - 75 lines]
>
> - Show quoted text -

Thanks, I will give it a try when I get home. After playing with the
array formula, it turns out that it takes way too long. The
spreadsheets I am working with contain between 10,000 and 20,000
rows. Excel was running for about 45 minutes, before I decided to end
the process.

-Andrew V. Romero

Reply to this Message

RagDyeR - 16 Oct 2007 15:46 GMT

You're copying that array formula down 20,000 rows?!?!?!
No wonder you're having this XL resource problem!

Peo's suggestion was aimed at a single row, and I suggested a revision to
enable it to go to 7 rows, to match your example.

You stated that in a "perfect world", you wanted to see *duplicate* results
per brand.
In your example, *each time* Toyota is displayed, you wanted the unique
count displayed.

How about just making a separate list of unique brands per lot?

How many brands do you have ... 10 - 20?
How many lots do you have ... 5 - 10?

That would make maybe 200 rows as opposed to 20,000 rows.

Post back if you're interested in this type of display?

Signature

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

On Oct 13, 6:47 pm, Traveller <Travel...@discussions.microsoft.com>
wrote:

> For what it's worth, I've sent you a sample file with my solution, in case
> you want to go that route. I added the VLOOKUP function to get the results
[quoted text clipped - 80 lines]
>
> - Show quoted text -

Thanks, I will give it a try when I get home. After playing with the
array formula, it turns out that it takes way too long. The
spreadsheets I am working with contain between 10,000 and 20,000
rows. Excel was running for about 45 minutes, before I decided to end
the process.

-Andrew V. Romero

Reply to this Message