1

I'm analyzing some survey data in which the responses are in the form of a string containing inequalities, e.g.

a) < 5

b) 5-10

c) 11-15

d) > 15

I'm attempting to tabulate responses using the Countifs function, and Excel does not appear to parse the value of the cell with the criteria argument as a string, and so returns 'False' for those bins; it does not evaluate a cell containing the string '< 5' as being equal to another cell containing '< 5', or the literal argument "< 5".

A comparison of the reference cells in other formulas, using = or EXACT() returns TRUE. (screenshot to show an example)

I know the criteria argument of the conditional operators like COUNTIF(), SUMIF(), etc. use strings with inequalities when they're actually serving as operators, but I can't figure out how to suppress this and evaluate these cells as simple strings. Thanks so much!

2

2 Answers 2

1

As stated The countifs in the criteria accepts string and thus <5 is reading less than five not the string.

To get it to look at the string use "="

=COUNTIF(C2,"="&C3)

By adding the = before the <, Excel sees it as a string and not an operand.

enter image description here

0

Instead of fighting the bugs of Excel and getting it to recognize the < symbol, I'd solve the problem by removing it altogether. Just replace <5 with something like 5_or_less and then COUNTIF should work.

=SUBSTITUTE(A1,"<5","5_or_less") will do the replacement for A1 and <5. You can put this in a new column, say B1. Then do another replacement for >15 in C1 like =SUBSTITUTE(B1,">15","15_or_more"). Then runCOUNTIFon columnC`.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .