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