COUNTIFS Incorrectly Returning 0

[Rachel_D]

I have two columns of data showing ordered shirt sizes and gifted shirt sizes, please see below. I entered each row by hand for this example, but double checked that no hidden characters are present via the trim() function. The formula =COUNTIFS(B2:B30, "S", C2:C30, "S") is returning a zero. I have changed columns B and C to text, general, etc. and nothing is fixing this issue. I thought perhaps the blanks were causing an error, but when I changed the ranges to match the shorter C column =COUNTIFS(B2:B11, "S", C2:C11, "S"), the formula is still incorrectly returning a zero. Thank you in advance for any assistance!

Row Number	Shirt Size	Gifted Shirt Size
2​	S	M
3​	M	S
4​	L	S
5​	S	M
6​	L	L
7​	L	S
8​	S	L
9​	S	M
10​	L	S
11​	S	L
12​	M
13​	L
14​	S
15​	M
16​	L
17​	S
18​	M
19​	M
20​	S
21​	M
22​	L
23​	L
24​	L
25​	L
26​	S
27​	M
28​	M
29​	S
30​	S

 

[Joe4]

Welcome to the Board!

It appears to be working correctly to me.
Your COUNTIFS formula will only count it if the "S" appears in both columns in the same row.
(Note what the COUNTIFS function does is count how many ROWS meet ALL your conditions!)
You do not have any rows in your example meeting both those conditions.

What exactly are you trying to count?

 

[awoohaw]

@Rachel_D What do you expect? Do you want to count of "S" in the first column if the second column corresponding cell is blank or "S"? Or do you want a count of all "S" in the entire range?

 

[Rachel_D]

Welcome to the Board!

It appears to be working correctly to me.
Your COUNTIFS formula will only count it if the "S" appears in both columns in the same row.
(Note what the COUNTIFS function does is count how many ROWS meet ALL your conditions!)
You do not have any rows in your example meeting both those conditions.

What exactly are you trying to count?

Thank you for the warm welcome and the speedy reply! I didn't realize COUNTIFS counted only when conditions are met in same row - this makes perfect sense now! I just wanted to count all the "S"s present in both columns, so a formula that will return 14 based on the sample data above. Thank you again, I really appreciate it!

 

[awoohaw]

then try this:

=SUM(--("S"=B2:C11))

or

=COUNTIF(B2:C11,"S")

(NOTE: I count 15 "S" in the above, is there some kind of exclusion process i've missed?)

 

[Joe4]

Thank you for the warm welcome and the speedy reply! I didn't realize COUNTIFS counted only when conditions are met in same row - this makes perfect sense now! I just wanted to count all the "S"s present in both columns, so a formula that will return 14 based on the sample data above. Thank you again, I really appreciate it!

Just use a COUNTIF to count all the "S" values in that entire range, i.e.

=COUNTIF(B2:C30,"S")