Counting unique entries based on given condition

[Hari]

Hi,

If I following data,

Comp Prod Unit
a x 1
b x 2
a x 2
a y 5


Then how can I know the number of unique companies for each type of
product (and the count of units)

I want something like this

Product UniqueComp SumOfUnits
x 2 5
y 1 5

I can do the SumOfUnits using SumIf formula but dont know how to
generate the Unique company counts for each product.

Also is it possible to get a Pivot based solution to this problem?

Regards
HP
India

[Ron Coderre]

Try this:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the Prod field here
DATA:
Drag the Comp field here
If it doesn't list as Count of Comp...dbl-click it and set it to Count
Drag the Unit field here
If it doesn't list as Sum of Unit...dbl-click it and set it to Sum

Click [OK]
Select where you want the Pivot Table

Not quite right yet...so....
Click and HOLD the Data column of the Pivot Table
Drag it on top of the Total heading....Release
Done!

That will list each Product with the Count of Companies and Sum of Units.

To refresh the Pivot Table, just right click it and select Refresh Data

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

XL2002, WinXP


"Hari" wrote:

> Hi,
>
> If I following data,
>
> Comp Prod Unit
> a x 1
> b x 2
> a x 2
> a y 5
>
>
> Then how can I know the number of unique companies for each type of
> product (and the count of units)
>
> I want something like this
>
> Product UniqueComp SumOfUnits
> x 2 5
> y 1 5
>
> I can do the SumOfUnits using SumIf formula but dont know how to
> generate the Unique company counts for each product.
>
> Also is it possible to get a Pivot based solution to this problem?
>
> Regards
> HP
> India
>
>

[Hari]

Ron,

Thanks for your post. The setup (format) of the solution is fine, but
in the "Count Of comp" I want only the count of Unique companies. So in
case of Product x would have only 2 (and not 3) as Count of Comp.

Is that possible?

Regards,
HP
India

Ron Coderre wrote:
> Try this:
>
> <Data><Pivot Table>
> Use: Excel
> Select your data
> Click the [Layout] button
>
> ROW: Drag the Prod field here
> DATA:
> Drag the Comp field here
> If it doesn't list as Count of Comp...dbl-click it and set it to Count
> Drag the Unit field here
> If it doesn't list as Sum of Unit...dbl-click it and set it to Sum
>
> Click [OK]
> Select where you want the Pivot Table
>
> Not quite right yet...so....
> Click and HOLD the Data column of the Pivot Table
> Drag it on top of the Total heading....Release
> Done!
>
> That will list each Product with the Count of Companies and Sum of Units.
>
> To refresh the Pivot Table, just right click it and select Refresh Data
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Hari" wrote:
>
> > Hi,
> >
> > If I following data,
> >
> > Comp Prod Unit
> > a x 1
> > b x 2
> > a x 2
> > a y 5
> >
> >
> > Then how can I know the number of unique companies for each type of
> > product (and the count of units)
> >
> > I want something like this
> >
> > Product UniqueComp SumOfUnits
> > x 2 5
> > y 1 5
> >
> > I can do the SumOfUnits using SumIf formula but dont know how to
> > generate the Unique company counts for each product.
> >
> > Also is it possible to get a Pivot based solution to this problem?
> >
> > Regards
> > HP
> > India
> >
> >

[Ron Coderre]

Yikes....I completely missed that!

As far as I know, there's no "count unique" functionality in a Pivot Table.

Maybe this will work for you...

When builiding the Pivot Table, put the Company field in the COLUMN section
(and remove it from the DATA section)
->That will list the companies in the column headings

Then, put the COUNT function to the right of of the Grand Total column to
count the number of cells under the Company listings that have numbers in
them...and, consequently, the count of companies for each product.

Note: if the GetPivtoData function appears and you don't want to use it...
See the instructions on how to toggle it on/of at Debra Dalgleish's website:
http://contextures.com/xlPivot06.html

Is that something you can work with?

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

XL2002, WinXP


"Hari" wrote:

> Ron,
>
> Thanks for your post. The setup (format) of the solution is fine, but
> in the "Count Of comp" I want only the count of Unique companies. So in
> case of Product x would have only 2 (and not 3) as Count of Comp.
>
> Is that possible?
>
> Regards,
> HP
> India
>
> Ron Coderre wrote:
> > Try this:
> >
> > <Data><Pivot Table>
> > Use: Excel
> > Select your data
> > Click the [Layout] button
> >
> > ROW: Drag the Prod field here
> > DATA:
> > Drag the Comp field here
> > If it doesn't list as Count of Comp...dbl-click it and set it to Count
> > Drag the Unit field here
> > If it doesn't list as Sum of Unit...dbl-click it and set it to Sum
> >
> > Click [OK]
> > Select where you want the Pivot Table
> >
> > Not quite right yet...so....
> > Click and HOLD the Data column of the Pivot Table
> > Drag it on top of the Total heading....Release
> > Done!
> >
> > That will list each Product with the Count of Companies and Sum of Units.
> >
> > To refresh the Pivot Table, just right click it and select Refresh Data
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Hari" wrote:
> >
> > > Hi,
> > >
> > > If I following data,
> > >
> > > Comp Prod Unit
> > > a x 1
> > > b x 2
> > > a x 2
> > > a y 5
> > >
> > >
> > > Then how can I know the number of unique companies for each type of
> > > product (and the count of units)
> > >
> > > I want something like this
> > >
> > > Product UniqueComp SumOfUnits
> > > x 2 5
> > > y 1 5
> > >
> > > I can do the SumOfUnits using SumIf formula but dont know how to
> > > generate the Unique company counts for each product.
> > >
> > > Also is it possible to get a Pivot based solution to this problem?
> > >
> > > Regards
> > > HP
> > > India
> > >
> > >
>
>

[Hari]

Ron,

Thanks again for the post.

Problem with putting the count function to right of the Total table is
that I would have to adjust the formula ranges for each product typr
manually which would defeat the purpose of making a pivot table.

Are there pure formula based solutions to this problem (without using
pivots). Probably some variation of an array sumproduct along with
"embedded" countif kind of formula?

Regards,
HP
India

Ron Coderre wrote:
> Yikes....I completely missed that!
>
> As far as I know, there's no "count unique" functionality in a Pivot Table.
>
> Maybe this will work for you...
>
> When builiding the Pivot Table, put the Company field in the COLUMN section
> (and remove it from the DATA section)
> ->That will list the companies in the column headings
>
> Then, put the COUNT function to the right of of the Grand Total column to
> count the number of cells under the Company listings that have numbers in
> them...and, consequently, the count of companies for each product.
>
> Note: if the GetPivtoData function appears and you don't want to use it...
> See the instructions on how to toggle it on/of at Debra Dalgleish's website:
> http://contextures.com/xlPivot06.html
>
> Is that something you can work with?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Hari" wrote:
>
> > Ron,
> >
> > Thanks for your post. The setup (format) of the solution is fine, but
> > in the "Count Of comp" I want only the count of Unique companies. So in
> > case of Product x would have only 2 (and not 3) as Count of Comp.
> >
> > Is that possible?
> >
> > Regards,
> > HP
> > India
> >
> > Ron Coderre wrote:
> > > Try this:
> > >
> > > <Data><Pivot Table>
> > > Use: Excel
> > > Select your data
> > > Click the [Layout] button
> > >
> > > ROW: Drag the Prod field here
> > > DATA:
> > > Drag the Comp field here
> > > If it doesn't list as Count of Comp...dbl-click it and set it to Count
> > > Drag the Unit field here
> > > If it doesn't list as Sum of Unit...dbl-click it and set it to Sum
> > >
> > > Click [OK]
> > > Select where you want the Pivot Table
> > >
> > > Not quite right yet...so....
> > > Click and HOLD the Data column of the Pivot Table
> > > Drag it on top of the Total heading....Release
> > > Done!
> > >
> > > That will list each Product with the Count of Companies and Sum of Units.
> > >
> > > To refresh the Pivot Table, just right click it and select Refresh Data
> > >
> > > Does that help?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP
> > >
> > >
> > > "Hari" wrote:
> > >
> > > > Hi,
> > > >
> > > > If I following data,
> > > >
> > > > Comp Prod Unit
> > > > a x 1
> > > > b x 2
> > > > a x 2
> > > > a y 5
> > > >
> > > >
> > > > Then how can I know the number of unique companies for each type of
> > > > product (and the count of units)
> > > >
> > > > I want something like this
> > > >
> > > > Product UniqueComp SumOfUnits
> > > > x 2 5
> > > > y 1 5
> > > >
> > > > I can do the SumOfUnits using SumIf formula but dont know how to
> > > > generate the Unique company counts for each product.
> > > >
> > > > Also is it possible to get a Pivot based solution to this problem?
> > > >
> > > > Regards
> > > > HP
> > > > India
> > > >
> > > >
> >
> >

[Peo Sjoblom]

You can use this method

http://www.contextures.com/xlPivot07.html#Unique

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Hari" <[email protected]> wrote in message
news:[email protected]...
> Ron,
>
> Thanks again for the post.
>
> Problem with putting the count function to right of the Total table is
> that I would have to adjust the formula ranges for each product typr
> manually which would defeat the purpose of making a pivot table.
>
> Are there pure formula based solutions to this problem (without using
> pivots). Probably some variation of an array sumproduct along with
> "embedded" countif kind of formula?
>
> Regards,
> HP
> India
>
> Ron Coderre wrote:
>> Yikes....I completely missed that!
>>
>> As far as I know, there's no "count unique" functionality in a Pivot
>> Table.
>>
>> Maybe this will work for you...
>>
>> When builiding the Pivot Table, put the Company field in the COLUMN
>> section
>> (and remove it from the DATA section)
>> ->That will list the companies in the column headings
>>
>> Then, put the COUNT function to the right of of the Grand Total column to
>> count the number of cells under the Company listings that have numbers in
>> them...and, consequently, the count of companies for each product.
>>
>> Note: if the GetPivtoData function appears and you don't want to use
>> it...
>> See the instructions on how to toggle it on/of at Debra Dalgleish's
>> website:
>> http://contextures.com/xlPivot06.html
>>
>> Is that something you can work with?
>>
>> ***********
>> Regards,
>> Ron
>>
>> XL2002, WinXP
>>
>>
>> "Hari" wrote:
>>
>> > Ron,
>> >
>> > Thanks for your post. The setup (format) of the solution is fine, but
>> > in the "Count Of comp" I want only the count of Unique companies. So in
>> > case of Product x would have only 2 (and not 3) as Count of Comp.
>> >
>> > Is that possible?
>> >
>> > Regards,
>> > HP
>> > India
>> >
>> > Ron Coderre wrote:
>> > > Try this:
>> > >
>> > > <Data><Pivot Table>
>> > > Use: Excel
>> > > Select your data
>> > > Click the [Layout] button
>> > >
>> > > ROW: Drag the Prod field here
>> > > DATA:
>> > > Drag the Comp field here
>> > > If it doesn't list as Count of Comp...dbl-click it and set it to
>> > > Count
>> > > Drag the Unit field here
>> > > If it doesn't list as Sum of Unit...dbl-click it and set it to Sum
>> > >
>> > > Click [OK]
>> > > Select where you want the Pivot Table
>> > >
>> > > Not quite right yet...so....
>> > > Click and HOLD the Data column of the Pivot Table
>> > > Drag it on top of the Total heading....Release
>> > > Done!
>> > >
>> > > That will list each Product with the Count of Companies and Sum of
>> > > Units.
>> > >
>> > > To refresh the Pivot Table, just right click it and select Refresh
>> > > Data
>> > >
>> > > Does that help?
>> > > ***********
>> > > Regards,
>> > > Ron
>> > >
>> > > XL2002, WinXP
>> > >
>> > >
>> > > "Hari" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > If I following data,
>> > > >
>> > > > Comp Prod Unit
>> > > > a x 1
>> > > > b x 2
>> > > > a x 2
>> > > > a y 5
>> > > >
>> > > >
>> > > > Then how can I know the number of unique companies for each type of
>> > > > product (and the count of units)
>> > > >
>> > > > I want something like this
>> > > >
>> > > > Product UniqueComp SumOfUnits
>> > > > x 2 5
>> > > > y 1 5
>> > > >
>> > > > I can do the SumOfUnits using SumIf formula but dont know how to
>> > > > generate the Unique company counts for each product.
>> > > >
>> > > > Also is it possible to get a Pivot based solution to this problem?
>> > > >
>> > > > Regards
>> > > > HP
>> > > > India
>> > > >
>> > > >
>> >
>> >
>

[Hari]

Peo,

Thanks for your link. It has been very helpul.

Regards,
HP
India

Peo Sjoblom wrote:
> You can use this method
>
> http://www.contextures.com/xlPivot07.html#Unique
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Hari" <[email protected]> wrote in message
> news:[email protected]...
> > Ron,
> >
> > Thanks again for the post.
> >
> > Problem with putting the count function to right of the Total table is
> > that I would have to adjust the formula ranges for each product typr
> > manually which would defeat the purpose of making a pivot table.
> >
> > Are there pure formula based solutions to this problem (without using
> > pivots). Probably some variation of an array sumproduct along with
> > "embedded" countif kind of formula?
> >
> > Regards,
> > HP
> > India
> >
> > Ron Coderre wrote:
> >> Yikes....I completely missed that!
> >>
> >> As far as I know, there's no "count unique" functionality in a Pivot
> >> Table.
> >>
> >> Maybe this will work for you...
> >>
> >> When builiding the Pivot Table, put the Company field in the COLUMN
> >> section
> >> (and remove it from the DATA section)
> >> ->That will list the companies in the column headings
> >>
> >> Then, put the COUNT function to the right of of the Grand Total column to
> >> count the number of cells under the Company listings that have numbers in
> >> them...and, consequently, the count of companies for each product.
> >>
> >> Note: if the GetPivtoData function appears and you don't want to use
> >> it...
> >> See the instructions on how to toggle it on/of at Debra Dalgleish's
> >> website:
> >> http://contextures.com/xlPivot06.html
> >>
> >> Is that something you can work with?
> >>
> >> ***********
> >> Regards,
> >> Ron
> >>
> >> XL2002, WinXP
> >>
> >>
> >> "Hari" wrote:
> >>
> >> > Ron,
> >> >
> >> > Thanks for your post. The setup (format) of the solution is fine, but
> >> > in the "Count Of comp" I want only the count of Unique companies. So in
> >> > case of Product x would have only 2 (and not 3) as Count of Comp.
> >> >
> >> > Is that possible?
> >> >
> >> > Regards,
> >> > HP
> >> > India
> >> >
> >> > Ron Coderre wrote:
> >> > > Try this:
> >> > >
> >> > > <Data><Pivot Table>
> >> > > Use: Excel
> >> > > Select your data
> >> > > Click the [Layout] button
> >> > >
> >> > > ROW: Drag the Prod field here
> >> > > DATA:
> >> > > Drag the Comp field here
> >> > > If it doesn't list as Count of Comp...dbl-click it and set it to
> >> > > Count
> >> > > Drag the Unit field here
> >> > > If it doesn't list as Sum of Unit...dbl-click it and set it to Sum
> >> > >
> >> > > Click [OK]
> >> > > Select where you want the Pivot Table
> >> > >
> >> > > Not quite right yet...so....
> >> > > Click and HOLD the Data column of the Pivot Table
> >> > > Drag it on top of the Total heading....Release
> >> > > Done!
> >> > >
> >> > > That will list each Product with the Count of Companies and Sum of
> >> > > Units.
> >> > >
> >> > > To refresh the Pivot Table, just right click it and select Refresh
> >> > > Data
> >> > >
> >> > > Does that help?
> >> > > ***********
> >> > > Regards,
> >> > > Ron
> >> > >
> >> > > XL2002, WinXP
> >> > >
> >> > >
> >> > > "Hari" wrote:
> >> > >
> >> > > > Hi,
> >> > > >
> >> > > > If I following data,
> >> > > >
> >> > > > Comp Prod Unit
> >> > > > a x 1
> >> > > > b x 2
> >> > > > a x 2
> >> > > > a y 5
> >> > > >
> >> > > >
> >> > > > Then how can I know the number of unique companies for each type of
> >> > > > product (and the count of units)
> >> > > >
> >> > > > I want something like this
> >> > > >
> >> > > > Product UniqueComp SumOfUnits
> >> > > > x 2 5
> >> > > > y 1 5
> >> > > >
> >> > > > I can do the SumOfUnits using SumIf formula but dont know how to
> >> > > > generate the Unique company counts for each product.
> >> > > >
> >> > > > Also is it possible to get a Pivot based solution to this problem?
> >> > > >
> >> > > > Regards
> >> > > > HP
> >> > > > India
> >> > > >
> >> > > >
> >> >
> >> >
> >

[Ron Coderre]

In case you're still interested in a formula approach, this seems to work:

With your data list contained in A1:C10

And
E1: Prod
F1: Comp Count
G1: Unit Total

E2:
=SUM(($B$1:$B$11=E2)*(E2<>"")*(FREQUENCY(($B$1:$B$10=E2)*MATCH($A$1:$A$10&$B$1:$B$10,$A$1:$A$10&$B$1:$B$10,0),($B$1:$B$10=E2)*MATCH($A$1:$A$10&$B$1:$B$10,$A$1:$A$10&$B$1:$B$10,0))>0))

F2:
=SUM(($B$1:$B$11=E2)*(FREQUENCY(($B$1:$B$10=E2)*MATCH($A$1:$A$10&$B$1:$B$10,$A$1:$A$10&$B$1:$B$10,0),($B$1:$B$10=E2)*MATCH($A$1:$A$10&$B$1:$B$10,$A$1:$A$10&$B$1:$B$10,0))>0))

G2: =SUMIF($B$1:$B$10,E2,$C$1:$C$10)

Note_1:
The formulss in E2 and F2 are ARRAY FORMULAS.
For array formulas, hold down [Ctrl] and [Shift] when you press [Enter],
instead of just pressing [Enter].

Note_2: In case text wrap impacts the display, there are NO spaces in those
formulas.

Next....
Copy E2:G2
Paste into E3:G3 and down as far as you need

Not especially elegant, but...Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Hari" wrote:

> Ron,
>
> Thanks again for the post.
>
> Problem with putting the count function to right of the Total table is
> that I would have to adjust the formula ranges for each product typr
> manually which would defeat the purpose of making a pivot table.
>
> Are there pure formula based solutions to this problem (without using
> pivots). Probably some variation of an array sumproduct along with
> "embedded" countif kind of formula?
>
> Regards,
> HP
> India
>
> Ron Coderre wrote:
> > Yikes....I completely missed that!
> >
> > As far as I know, there's no "count unique" functionality in a Pivot Table.
> >
> > Maybe this will work for you...
> >
> > When builiding the Pivot Table, put the Company field in the COLUMN section
> > (and remove it from the DATA section)
> > ->That will list the companies in the column headings
> >
> > Then, put the COUNT function to the right of of the Grand Total column to
> > count the number of cells under the Company listings that have numbers in
> > them...and, consequently, the count of companies for each product.
> >
> > Note: if the GetPivtoData function appears and you don't want to use it...
> > See the instructions on how to toggle it on/of at Debra Dalgleish's website:
> > http://contextures.com/xlPivot06.html
> >
> > Is that something you can work with?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Hari" wrote:
> >
> > > Ron,
> > >
> > > Thanks for your post. The setup (format) of the solution is fine, but
> > > in the "Count Of comp" I want only the count of Unique companies. So in
> > > case of Product x would have only 2 (and not 3) as Count of Comp.
> > >
> > > Is that possible?
> > >
> > > Regards,
> > > HP
> > > India
> > >
> > > Ron Coderre wrote:
> > > > Try this:
> > > >
> > > > <Data><Pivot Table>
> > > > Use: Excel
> > > > Select your data
> > > > Click the [Layout] button
> > > >
> > > > ROW: Drag the Prod field here
> > > > DATA:
> > > > Drag the Comp field here
> > > > If it doesn't list as Count of Comp...dbl-click it and set it to Count
> > > > Drag the Unit field here
> > > > If it doesn't list as Sum of Unit...dbl-click it and set it to Sum
> > > >
> > > > Click [OK]
> > > > Select where you want the Pivot Table
> > > >
> > > > Not quite right yet...so....
> > > > Click and HOLD the Data column of the Pivot Table
> > > > Drag it on top of the Total heading....Release
> > > > Done!
> > > >
> > > > That will list each Product with the Count of Companies and Sum of Units.
> > > >
> > > > To refresh the Pivot Table, just right click it and select Refresh Data
> > > >
> > > > Does that help?
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2002, WinXP
> > > >
> > > >
> > > > "Hari" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > If I following data,
> > > > >
> > > > > Comp Prod Unit
> > > > > a x 1
> > > > > b x 2
> > > > > a x 2
> > > > > a y 5
> > > > >
> > > > >
> > > > > Then how can I know the number of unique companies for each type of
> > > > > product (and the count of units)
> > > > >
> > > > > I want something like this
> > > > >
> > > > > Product UniqueComp SumOfUnits
> > > > > x 2 5
> > > > > y 1 5
> > > > >
> > > > > I can do the SumOfUnits using SumIf formula but dont know how to
> > > > > generate the Unique company counts for each product.
> > > > >
> > > > > Also is it possible to get a Pivot based solution to this problem?
> > > > >
> > > > > Regards
> > > > > HP
> > > > > India
> > > > >
> > > > >
> > >
> > >
>
>

[Domenic]

In article <[email protected]>,
"Hari" <[email protected]> wrote:

> Are there pure formula based solutions to this problem (without using
> pivots). Probably some variation of an array sumproduct along with
> "embedded" countif kind of formula?

Assuming that A2:C5 contains the data, let E2 and E3 contain x and y,
respectively, then try the following...

For a unique count, if you download and install the free add-in
Morefunc.xll...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=COUNTDIFF(IF($B$2:$B$5=E2,$A$2:$A$5,0),FALSE,0)

Otherwise...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=COUNT(1/FREQUENCY(IF($B$2:$B$5=E2,IF($A$2:$A$5<>"",MATCH($A$2:$A$5,$A$2:
$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1))

To sum units...

G2, copied down:

=SUMIF($B$2:$B$5,E2,$C$2:$C$5)

Hope this helps!

[Ron Coderre]

Hey, Domenic

Re:
=COUNT(1/FREQUENCY(IF($B$2:$B$5=E2,IF($A$2:$A$5<>"",MATCH($A$2:$A$5,$A$2:$A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1))

After looking at THAT formula....I don't know what in world drove me to use
the clunky approach I used!

Nice work.
***********
Best Regards,
Ron

XL2002, WinXP


"Domenic" wrote:

> In article <[email protected]>,
> "Hari" <[email protected]> wrote:
>
> > Are there pure formula based solutions to this problem (without using
> > pivots). Probably some variation of an array sumproduct along with
> > "embedded" countif kind of formula?
>
> Assuming that A2:C5 contains the data, let E2 and E3 contain x and y,
> respectively, then try the following...
>
> For a unique count, if you download and install the free add-in
> Morefunc.xll...
>
> F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:
>
> =COUNTDIFF(IF($B$2:$B$5=E2,$A$2:$A$5,0),FALSE,0)
>
> Otherwise...
>
> F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:
>
> =COUNT(1/FREQUENCY(IF($B$2:$B$5=E2,IF($A$2:$A$5<>"",MATCH($A$2:$A$5,$A$2:
> $A$5,0))),ROW($A$2:$A$5)-ROW($A$2)+1))
>
> To sum units...
>
> G2, copied down:
>
> =SUMIF($B$2:$B$5,E2,$C$2:$C$5)
>
> Hope this helps!
>