Formula: Counting Cell Values with symbols

Options

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)

Tags:

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Options

    @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

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Justin S.
    Options

    Thanks Brent,

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!