Counting unique entries based on given condition
 

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

Counting unique entries based on given condition
 

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

Counting unique entries based on given condition
 

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

Counting unique entries based on given condition
 

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

Counting unique entries based on given condition
 

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

Counting unique entries based on given condition
 

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" wrote in message
oups.com...
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

Counting unique entries based on given condition
 

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" wrote in message
oups.com...
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

Counting unique entries based on given condition
 

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)*MA TCH($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

Counting unique entries based on given condition
 

In article .com,
"Hari" 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!

Counting unique entries based on given condition
 

Hey, Domenic


=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 .com,
"Hari" 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!