Excel sheet - need to count occurances of Words

ublinia2

I have an excel sheet , basically showing my phone calls from O2.ie that I have made over the past month .

In different columns the spreadsheet shows different information .ie IN A column date of call , b time of call, c which number i dialled , D how long the call was and in E the type it was ie phone call, sms or gprs.

I want to use this sheet to see how many texts , calls and gprs i used during the last month.

I think i need a formula to count how many time 'sms' occurs in a certain column range - from (G7 to G300) say. and similar ones for calls , gprs etc.

Can anybody think of a formula or expression which will do this?

timeout

Put the below formula into a spare cell somewhere -

=COUNTIF(G7:G300,"sms")

Now this only works if the text is sms, if its gsms it won't.
Timeout

seamus

timeout wrote:

Put the below formula into a spare cell somewhere -

=COUNTIF(G7:G300,"sms")

Now this only works if the text is sms, if its gsms it won't.
Timeout

Never heard of gsms myself, but if such a thing appears on the bill, a slight modification should catch it:

=COUNTIF(G7:G300,"*sms")

timeout

seamus wrote:

Never heard of gsms myself, but if such a thing appears on the bill, a slight modification should catch it:

=COUNTIF(G7:G300,"*sms")

True, you could make it:

=COUNTIF(G7:G300,"*sms*")

just in case its smsg
You never know when them typos might occur!

japjap

Easiest (and crudest) way is just to highlight everything, then go to Data > Sort. This will bring up a box with "Sort by [Column A] then by [Column B] then by etc.

Just change the boxes to sort by whatever column is most important, ie SMS

If you just highlight the data (not columns) it looks better.

japjap

Correction -

If you just highlight the data (not column headers/titles) it looks better

ublinia2

Thanks , this works perfectly.

Anybody know how i could analyse the number of calls and for how long that i make between 8 am and 6pm , and calls I make from 6pm to 8pm ? using the data i have in column A in date , B the time of call , C for how long etc?

Anybody know How I could track the top five numbers I dialled and cumulate for how long each month I spend phoning that number?

Any help would be much appreciated- Thanks

timeout

I have not seen any O2 phone bill so I am not famillir with the layout. If you could post up a sample file using the exact wording but not exact values we could be of more help. Example below.

type     duration     time     number
call        1:21         18:23     0861234567
sms        0:00         19:03    0867654321
ect......

kincsem

You could try something like this.

Suppose you have a column of numbers in cells B5 to B24.

Put this formula in a cell below the column of numbers to be added. This formula will add the three biggest numbers
=SUMIF($A$5:$A$24,">"&LARGE($B$5:$B$24,4),$B$5:$B$24)

What you are saying is lookup the range $B$5:$B$24,..... for numbers greater than the fourth largest ">"&LARGE($B$5:$B$24,4) .... and sum the numbers in the range $B$5:$B$24 that meet that condition.

You could put this formula in a column adjacent to your original column e.g. $C$5:$C$24 and it will rank the values in order =RANK($B5,$B$5:$B$24,0). Copy this formula down from cell B5 to B24.

I'm a poker player who wandered into the wrong forum.


You can also format the cell in different colours using conditional formatting. You use the above formulas or something similar. Ask if you need help.

Place the cursor on the cell ......... You go Conditional formatting ..... Condition 1 ...... "Cell value is" ....... "greater than or equal to" ........ = LARGE($B$5:$B$24,3) ....... Click the format button on the right .... and select a colour ..... then OK ..... OK

When you have that done for one cell you click the ... Format Painter ... button on the Excel Standard toolbar and highlight the cells to paint with that conditional formatting.

ublinia2

timeout wrote:

I have not seen any O2 phone bill so I am not famillir with the layout. If you could post up a sample file using the exact wording but not exact values we could be of more help. Example below.

type     duration     time     number
call        1:21         18:23     0861234567
sms        0:00         19:03    0867654321
ect......

Heres An example below

Column A Column B Column C Column D COl E COL F
DATE TIME Number Duration Type Charge
20/02/2006 19:00:00 <1800100200> 00:05:22 Voice 0
20/02/2006 21:22:53 <35386123456> SMS 0
20/02/2006 21:26:11 <35387987654> SMS 0
20/02/2006 21:59:46 GPRS 0.13
20/02/2006 22:02:14 GPRS 0.04
21/02/2006 08:22:21 <35387911111> 00:00:07 Voice 0.02
21/02/2006 08:22:35 <35387987654> 00:00:09 Voice 0.03
21/02/2006 08:23:25 <35387987654> 00:00:25 Voice 0
21/02/2006 09:45:56 <35301456789> 00:00:28 Voice 0.07

ublinia2

kincsem wrote:

You could try something like this.

Suppose you have a column of numbers in cells B5 to B24.

Put this formula in a cell below the column of numbers to be added. This formula will add the three biggest numbers
=SUMIF($A$5:$A$24,">"&LARGE($B$5:$B$24,4),$B$5:$B$24)

What you are saying is lookup the range $B$5:$B$24,..... for numbers greater than the fourth largest ">"&LARGE($B$5:$B$24,4) .... and sum the numbers in the range $B$5:$B$24 that meet that condition.

You could put this formula in a column adjacent to your original column e.g. $C$5:$C$24 and it will rank the values in order =RANK($B5,$B$5:$B$24,0). Copy this formula down from cell B5 to B24.

I have tried this on the sample numbers I have given below , but it doesnt seem to work , for the sample i have below , i am getting a value of 14.86864583 and for the rank part a value of 6 ??

kincsem

Sorry. I should have used your data yesterday.

Assuming DATE is in cells A3 to A11
Assuming TIME is in cells B3 to B11
Assuming Number is in cells C3 to C11
Assuming Duration is in cells D3 to D11
Assuming Type is in cells E3 to E11
Assuming Charge is in cells F3 to F11

TO COUNT THE CALLS OF EACH TYPE

Type “Voice” in A13
Type “SMS” in A14
Type “GPRS” in A15

Type these formulas in cells E13, E14, E15
=COUNTIF($E$3:$E$11,A13)
=COUNTIF($E$3:$E$11,A14)
=COUNTIF($E$3:$E$11,A15)

NUMBER OF CALLS, DURATION, AND CHARGE

Type “From” in cell A19
Type “To” in cell B19

Type 00:00:00 in cell A20
Type 08:00:00 in cell B20
Type 08:00:00 in cell A21
Type 18:00:00 in cell B21
Type 18:00:00 in cell A22
Type 20:00:00 in cell B22
Type 20:00:00 in cell A23
Type 24:00:00 in cell B23

NUMBER OF CALLS

In cell C20 type
=COUNTIF($B$3:$B$11,">"&A20)-COUNTIF($B$3:$B$11,">"&B20)
In cell C21 type
=COUNTIF($B$3:$B$11,">"&A21)-COUNTIF($B$3:$B$11,">"&B21)
In cell C22 type
=COUNTIF($B$3:$B$11,">"&A22)-COUNTIF($B$3:$B$11,">"&B22)
In cell C23 type
=COUNTIF($B$3:$B$11,">"&A23)-COUNTIF($B$3:$B$11,">"&B23)

DURATION OF CALLS

I’ll let you work this out yourself using the calls charge example below.

CHARGE FOR CALLS

In cell F20 type
=SUMIF($B$3:$B$11,">"&A20,$F$3:$F$11)-SUMIF($B$3:$B$11,">"&B20,$F$3:$F$11)
Then in F21, F22, F23
=SUMIF($B$3:$B$11,">"&A21,$F$3:$F$11)-SUMIF($B$3:$B$11,">"&B21,$F$3:$F$11)
=SUMIF($B$3:$B$11,">"&A22,$F$3:$F$11)-SUMIF($B$3:$B$11,">"&B22,$F$3:$F$11)
=SUMIF($B$3:$B$11,">"&A23,$F$3:$F$11)-SUMIF($B$3:$B$11,">"&B23,$F$3:$F$11)

If there is a problem it will be will the data type of the TIME column. You can use /Data/Text To Columns from the menus to parse the time as date (time) (take care as that menu option also tries to parse the column to the right also - select "do not import column" for that).