Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: COUNTIFS not counting

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIFS not counting

    Hi
    I'm learning about COUNTIFS and I don't understand why this is working in one row and not another.

    In my first cell in question My Criteria is TECH (E4) in Ranges A2 to A20 and R(G4) is ranges B2 to B20 :

    I'm using =COUNTIFS($A2:$A20,$E4,$B2:$B20,G4) - and it seems to work, it counts 4 occurrences of R

    On a different cell I want to count the following criteria BUS (E9) and R (G9)

    =COUNTIFS($A2:$A20,$E9,$B2:$B20,G9) and it is not counting the Rs.

    Can anyone shed light on this? Be gentle I am a beginner.

    Thanks
    Ian




    OAC Colour
    TECH R
    TECH A
    TECH G TECH(E4) R A G
    TECH R 4 2 2
    TECH A
    TECH G
    TECH R
    TECH R BUS(E9) R A G
    BUS A 0 0 0
    BUS R
    BUS A
    BUS G
    BUS R
    BUS A
    BUS G
    BUS R
    BUS G
    BUS G
    BUS A

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: COUNTIFS not counting

    Welcome to the Board!

    I copied your data and formulas and it seems to work for me.

    Try entering these two formula in any blank sheet and tell me what they return:
    =LEN(E9)
    =LEN(A10)
    Last edited by Joe4; Sep 12th, 2019 at 10:35 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: COUNTIFS not counting

    Seems to work for me

    ABCDEFGHI
    1OACColour
    2TECHR
    3TECHA
    4TECHGTECHRAG
    5TECHR422
    6TECHA
    7TECHG
    8TECHR
    9TECHRBUSRAG
    10BUSA344
    11BUSR
    12BUSA
    13BUSG
    14BUSR
    15BUSA
    16BUSG
    17BUSR
    18BUSG
    19BUSG
    20BUSA

    Sheet4



    Worksheet Formulas
    CellFormula
    G5=COUNTIFS($A2:$A20,$E4,$B2:$B20,G4)
    G10=COUNTIFS($A2:$A20,$E9,$B2:$B20,G9)



    What if you try this in an empty cell
    =COUNTIF(A2:A20,E9)

    What does it return?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS not counting

    Quote Originally Posted by Fluff View Post
    Seems to work for me

    A B C D E F G H I
    1 OAC Colour
    2 TECH R
    3 TECH A
    4 TECH G TECH R A G
    5 TECH R 4 2 2
    6 TECH A
    7 TECH G
    8 TECH R
    9 TECH R BUS R A G
    10 BUS A 3 4 4
    11 BUS R
    12 BUS A
    13 BUS G
    14 BUS R
    15 BUS A
    16 BUS G
    17 BUS R
    18 BUS G
    19 BUS G
    20 BUS A
    Sheet4

    Worksheet Formulas
    Cell Formula
    G5 =COUNTIFS($A2:$A20,$E4,$B2:$B20,G4)
    G10 =COUNTIFS($A2:$A20,$E9,$B2:$B20,G9)



    What if you try this in an empty cell
    =COUNTIF(A2:A20,E9)

    What does it return?
    Thank you. I just tried this It returns 0

    If I change it to = COUNTIF(A2:A20,E4) it returns 8

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS not counting

    thank you.

    =LEN(E9) returns TRUE
    =LEN (A10) returns 0

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS not counting

    Thanks for assisting - E9 returns 0 and E4 returns 8 !?

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: COUNTIFS not counting

    =LEN(E9) returns TRUE
    =LEN (A10) returns 0
    If that is true, then you have real problems, because according to your example, both E9 and A10 should have the string "BUS" in them, so they should both return a value of 3.
    If they do not, then you have some issues somewhere (incorrect range references) and your formula will definitely not work, as written.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: COUNTIFS not counting

    Does E4 contain TECH or TECH(E4)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    New Member
    Join Date
    Sep 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS not counting

    Sorry just tried again,
    LEN(E9)returns 3
    LEN(A10) returns 4?

  10. #10
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: COUNTIFS not counting

    LEN(A10) returns 4?
    Sounds like you may have an extra space at the beginning or end of the entry in A10.
    Note that it MUST match exactly. "BUS" does not equal "BUS ".
    You can do Find/Replace on column A to quickly remove all spaces (replace a single space with nothing).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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