Prev: PLACING NON PRINTABLE WATER MARK IN WORK SHEET
Next: Can I increase the weight of a font in Excel?
From: viraj_av on 2 Jun 2010 05:14
I have a sheet with following data
Names
__________________________________
A B C D E F
1 ball bell cat dog hat top
2 ball cat jug man sun
NO 3 ball dog
4 hat
5 top cat


I tried to creat pivot table to count name in the range A1:F5

But the result I got was
Count of Name
Name Grand Total
ball 3 3
hat 1 1
top 1 1
blank


The pivot Table didnot consider names in column B,C,D,E & F

Please advice me how can i get the table as shown bellow

ball 3
cat 3
dog 2
hat 2
jug 1
man 1
sun 1
14

From: Roger Govier on 2 Jun 2010 05:23
Hi

Your data is not in a format that is suitable for analysis with a Pivot
Table in this way.
Create your List of Items in A1:A7 of Sheet2
In B1 of Sheet2 enter
=COUNTIF(Sheet1!$A:$F,$A1)
Copy down through B2:B7

--
-------
Regards
Roger Govier

"viraj_av" <viraj_av(a)discussions.microsoft.com> wrote in message
news:B4C41215-7840-4466-B7D5-3FF5A029DF13(a)microsoft.com...
> I have a sheet with following data
> Names
> __________________________________
> A B C D E F
> 1 ball bell cat dog hat top
> 2 ball cat jug man sun
> NO 3 ball dog
> 4 hat
> 5 top cat
>
>
> I tried to creat pivot table to count name in the range A1:F5
>
> But the result I got was
> Count of Name
> Name Grand Total
> ball 3 3
> hat 1 1
> top 1 1
> blank
>
>
> The pivot Table didnot consider names in column B,C,D,E & F
>
> Please advice me how can i get the table as shown bellow
>
> ball 3
> cat 3
> dog 2
> hat 2
> jug 1
> man 1
> sun 1
> 14
>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5164 (20100601) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 5164 (20100601) __________

The message was checked by ESET Smart Security.

http://www.eset.com



 | 
Pages: 1
Prev: PLACING NON PRINTABLE WATER MARK IN WORK SHEET
Next: Can I increase the weight of a font in Excel?