+ Reply to Thread
Results 1 to 7 of 7

Countif function not counting text

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Countif function not counting text

    I am using several countif formulas to count the number of appointments each consultant has seen. So it is essentially the same formula for each consultant, just changing the name for each different one. This works for every consultant, except one! (Which happens to be mine) It doesn't count the appointments for Liz
    Example of formula:
    =COUNTIFS(E2:E19,"Liz",H2:H19,"1")+COUNTIFS(E2:E19,"Liz",I2:I19,"1")+COUNTIFS(E28:E36,"Liz",H28:H36,"1")+COUNTIFS(E28:E36,"Liz",I28:I36,"1")+COUNTIFS(E55:E65,"Liz",H55:H65,"1")+COUNTIFS(E55:E65,"Liz",I55:I65,"1")+COUNTIFS(E84:E93,"Liz",H84:H93,"1")+COUNTIFS(E84:E93,"Liz",I84:I93,"1")+COUNTIFS(E113:E131,"Liz",H113:H131,"1")+COUNTIFS(E113:E131,"Liz",I113:I131,"1")+COUNTIFS(E142:E163,"Liz",H142:H163,"1")+COUNTIFS(E142:E163,"Liz",I142:I163,"1")

    I know it's long, i don't know how else to do it :| So this same formula works for all the other names except for "Liz" - does anyone have any ideas?
    I have deleted all the Liz's in the spreadsheet and re-entered them and deleted the formula and re-done by it's not working.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,794

    Re: Countif function not counting text

    Perhaps Liz has been entered with a space at the end? You can use =LEN(E2) to check for how many characters you have used.

    Hope this helps.

    Pete

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,554

    Re: Countif function not counting text

    I'd actually have thought it was the "1" ... expecting it to be just numeric 1 but for it working elsewhere. No leading or trailing spaces?

    Have you really hard coded your name into the formula so many times?

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    09-11-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Countif function not counting text

    Thanks. I checked and there are only 3 characters, no spaces. There are actually more occurrences than the formula I put on here, i just shortened it for this thread!
    But the "1" works for all the other consultants, e.g.. I use the exact same formula just with different names and it counts the appointments for them just not this one

  5. #5
    Registered User
    Join Date
    09-11-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Countif function not counting text

    So any other possible solutions would be appreciated

  6. #6
    Registered User
    Join Date
    09-11-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Countif function not counting text

    Sorry i tried deleting and adding again and it worked, thank you anyway

  7. #7
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Countif function not counting text

    maybe try with wild cards first if this gives the correct values Pete is right w/c i also suspects.

    "*Liz*"
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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