COUNTIFS not counting

[IANWILLZ]

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




[TABLE="width: 776"]
<tbody>[TR]
[TD]OAC[/TD]
[TD]Colour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TECH[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TECH[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TECH[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD]TECH(E4)[/TD]
[TD][/TD]
[TD]R[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]TECH[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]TECH[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TECH[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TECH[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TECH[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD]BUS(E9)[/TD]
[TD][/TD]
[TD]R[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]R[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BUS[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

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)

 

[Fluff]

Seems to work for me

Book1
	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

Cell Formulas
Range	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?

 

[IANWILLZ]

Seems to work for me

	A	B	C	D	E	F	G	H	I
OAC	Colour
TECH	R
TECH	A
TECH	G	TECH	R	A	G
TECH	R
TECH	A
TECH	G
TECH	R
TECH	R	BUS	R	A	G
BUS	A
BUS	R
BUS	A
BUS	G
BUS	R
BUS	A
BUS	G
BUS	R
BUS	G
BUS	G
BUS	A

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=COUNTIFS($A2:$A20,$E4,$B2:$B20,G4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G10[/TH]
[TD="align: left"]=COUNTIFS($A2:$A20,$E9,$B2:$B20,G9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

 

[IANWILLZ]

thank you.

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

 

[IANWILLZ]

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

 

[Joe4]

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

 

[Fluff]

Does E4 contain TECH or TECH(E4)

 

[IANWILLZ]

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

 

[Joe4]

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