Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel sheet - need to count occurances of Words

  • 21-02-2006 2:01pm
    #1
    Closed Accounts Posts: 114 ✭✭


    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?


Comments

  • Registered Users, Registered Users 2 Posts: 919 ✭✭✭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


  • Registered Users, Registered Users 2 Posts: 68,317 ✭✭✭✭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")


  • Registered Users, Registered Users 2 Posts: 919 ✭✭✭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 :D
    You never know when them typos might occur!


  • Registered Users, Registered Users 2 Posts: 79 ✭✭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.


  • Registered Users, Registered Users 2 Posts: 79 ✭✭japjap


    Correction -

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


  • Advertisement
  • Closed Accounts Posts: 114 ✭✭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


  • Registered Users, Registered Users 2 Posts: 919 ✭✭✭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......
    


  • Closed Accounts Posts: 4,013 ✭✭✭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.


  • Closed Accounts Posts: 114 ✭✭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


  • Closed Accounts Posts: 114 ✭✭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 ??


  • Advertisement
  • Closed Accounts Posts: 4,013 ✭✭✭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).


Advertisement