count text appear how many times & put in respec col??eg 1st times

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Piglet - 29 May 2008 03:29 GMT

Need to found out the text eg Movie appeared how many times in a worksheet.
If 1 time = 1R , 2 time = 2R, 3rd times = 3r etc

Reply to this Message

pdberger - 29 May 2008 03:38 GMT

Piglet --

A B
1 Movie
2 Movie
3 Dog
4 Cat
5
6 =COUNTIF(A1:A4,"Movie")

Brings a result of 2.

HTH

> Need to found out the text eg Movie appeared how many times in a worksheet.
> If 1 time = 1R , 2 time = 2R, 3rd times = 3r etc

Reply to this Message

Piglet - 29 May 2008 03:49 GMT

how the result show in the respective column? the 1st times in appeared in
the worksheet show in column A and 2nd times in column b

Eg
Amt A (1R) B(2R) C
(3R)
1 Movie S$1000 S$1000
2 Movie S$5000 S$5000
3 Drama S$3000 S$3000
4 Drama S$500 S$500
5 Movie S$8000
S$8000
4

> Need to found out the text eg Movie appeared how many times in a worksheet.
> If 1 time = 1R , 2 time = 2R, 3rd times = 3r etc

Reply to this Message

Roger Govier - 29 May 2008 07:53 GMT

Hi

With your source data in columns A and B with headings of Type and Amount,
add a new heading to column C called Frequency
In C2 enter
=COUNTIF($A$2:A2,A2)
and copy down for your entire set of data

Place your cursor on the header row>data>Pivot Table>Finish
On the new skeleton Pivot Table grid that appears,
drag Type to the Row area
drag Frequency to the column area
drag Amount to the data area

Signature

Regards
Roger Govier

> how the result show in the respective column? the 1st times in appeared
> in
[quoted text clipped - 15 lines]
>> worksheet.
>> If 1 time = 1R , 2 time = 2R, 3rd times = 3r etc

Reply to this Message