Formula: Counting Cell Values with symbols
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)
Answers
-
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
-
Thanks Brent,
It does look like the formula just really doesn't like the special character, despite the quotations.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!