Formula: Counting Cell Values with symbols

Justin S.

Hello,

I have a formula to count the entries for "Actual Hours" within a date range. For one of the values "<.5" the formula returns zero. I've tested a change to "lessthan.5" which counts as desired. I went back and ensured there were no erroneous spaces in the "<.5" value entry.

Is this a limitation with formulas or have I missed something?

Returns 0:

=COUNTIFS({Actual Hours}, "<.5", {Completed Date}, >=[Date Range Start]1, {Completed Date}, <=[Date Range End]1)

Returns 2 (as expected):

=COUNTIFS({Actual Hours}, "lessthan.5", {Completed Date}, >=[Date Range Start]1, {Completed Date}, <=[Date Range End]1)

Brent Wilson

@Justin S.

Be careful as you are using a text and number

"<.5", "<0.5", and <.5 are all different values

It looks like you are evaluating a text value in quotes vs a number out of quotes

=COUNTIFS({Actual Hours}, ="<.5", {Completed Date}, >=[Date Range Start]1, {Completed Date}, <=[Date Range End]1)

It is general programming practice you should not include things like *, <, -, =, etc.. as depending on the programming language it can cause errors

Just a guess if lessthan.5 works and <.5 doesn't. Excel does weird things too if you have noticed where it thinks certain things are numbers and others are text..

don't know if that helps

Justin S.

Thanks Brent,

It does look like the formula just really doesn't like the special character, despite the quotations.