# Thread: COUNTIFS not counting Thanks: 0 Likes:  1 Post #5340905 (1)

1. ## 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. ## 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)

3. ## 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?

4. ## Re: COUNTIFS not counting

Originally Posted by Fluff
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. ## Re: COUNTIFS not counting

thank you.

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

6. ## Re: COUNTIFS not counting

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

7. ## 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.

8. ## Re: COUNTIFS not counting

Does E4 contain TECH or TECH(E4)

9. ## Re: COUNTIFS not counting

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

10. ## 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).