+ Reply to Thread
Results 1 to 7 of 7

Help with counting the number of times a certain text appears

  1. #1
    Registered User
    Join Date
    09-03-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    7

    Help with counting the number of times a certain text appears

    Hi there,

    I have a column B which will have text appear and want to count how many times it shows


    I have the formula =sum(IF( Tally!B6:B80="facebook" 1,0)) and it counts the first entry if facebook but then doesn't count the rest


    What I'm I doing wrong ????

    Sorry think I have found it while I was writing this


    =COUNTIF(Tally!B6:B80,"Facebook")

    tried it and it works
    Last edited by buckles23; 09-04-2014 at 09:42 AM.

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with counting the number of times a certain text appears

    It's good that you found another formula (which is better one to use in this scenario) to work for you. But at the same time you must also know that why the earlier formula (sum(IF( Tally!B6:B80="facebook" 1,0))) didn't work for you. That is because this formula is an Array Formula which requires special keystrokes Ctrl+Shift+Enter instead of just Enter which you normally use in case of a regular excel formula. i.e. after typing the formula in the cell, hold down the Ctrl+Shift together and press Enter. When you confirm a formula as an array formula, you will notice in the formula bar that the formula gets surrounded by the Curly Brackets and if you don't see the curly brackets in the formula bar, press F2 (Function Key), hold down the Ctrl+Shift together and then press Enter to re-enter the formula as an array formula.
    Hope this helps.
    Last edited by sktneer; 09-04-2014 at 09:06 AM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    09-03-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    7

    Re: Help with counting the number of times a certain text appears

    Thanks mate .. I have see something about that but the Ctrl shift enter did seems to work but will try your other method..

    Defo learning being on here !!

  4. #4
    Registered User
    Join Date
    09-03-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    7

    Re: Help with counting the number of times a certain text appears

    I have an other issues now with extending this formula to count the number of outcomes with "Read Only" which are on the same row as the Facebook outcome..

    =SUMIF(Tally!$B$6:$B$87,"Facebook",Tally!$O$6:$O$87)

    So the above will Add up all the Timestamps in O column, which works fine !!

    So need to change the Tally!$O$6:$O$87 to count the number of times "ready only" appear in Column K... ???

    =SUMIF(Tally!B6:B87,"Facebook",Tally!K6:K81,"Read Only") -- Error message to say too many arguments !!

    Can't get my head round it ...

    My head is thinking...... =COUNTIF(Tally!K6:K87,"Read Only" if column B says facebook) but not sure how the write the second bit .. lol
    Last edited by buckles23; 09-04-2014 at 09:55 AM.

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with counting the number of times a certain text appears

    Replace SUMIF with COUNTIFS. Remember SUMIF requires only three argument i.e. Range, Criteria and SumRange and you passed four arguments in the formula. Moreover Sumif is used to sum a range based on a criteria, while you are trying to count the occurrences based on two criteria. Therefor you should use Countifs rather than Sumif. Moreover in the Countifs range reference should be of the same size.

  6. #6
    Registered User
    Join Date
    09-03-2014
    Location
    Scotland
    MS-Off Ver
    2007
    Posts
    7

    Re: Help with counting the number of times a certain text appears

    No Sure I get that....

    So I have the Formula...... =COUNTIFS(Tally!$B$6:$B$87,"Facebook",Tally!K6:K81,"Read Only") Is this not counting both facebook and ready only as giving #value!

    How do I change this ?

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Help with counting the number of times a certain text appears

    The problem in your formula (COUNTIFS(Tally!$B$6:$B$87,"Facebook",Tally!K6:K81,"Read Only")) is the size of two ranges which are not same. As I told you in my previous post that the ranges used in the countifs should be of the same size. Therefore the correct formula would be like this...

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Counting number of times unique id appears
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-26-2012, 01:06 PM
  2. Replies: 3
    Last Post: 05-14-2012, 10:22 PM
  3. Replies: 2
    Last Post: 03-14-2012, 08:12 AM
  4. Counting the number of times a month appears
    By Kololo in forum Excel General
    Replies: 3
    Last Post: 02-24-2012, 10:29 PM
  5. Counting the number of times an entry appears
    By nickifan in forum Excel General
    Replies: 2
    Last Post: 11-22-2010, 12:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1