COUNTIF showing "0" when referencing a column where cells have formula
Hello!
Please help! First I have one sheet (Pre-Service) with several columns, three of them used here, the first two are dates, and the third is a calculation to determine the number of weeks between the two dates. I used (=(([Anticipated Graduation date]@row) - ([Pre-Service Class Start Date]@row)) / 7).
So far so good. When using a calculation sheet, to group the number of weeks for the entire column (to create a chart), with the calculation: =COUNTIF({Pre-Service Range 4}, "7") the result is "0". I have also tried =COUNTIF({Pre-Service Range 4}, "=7"), =COUNTIF({Pre-Service Range 4}, 7) and they all return "0".
Best Answer
-
Finally figured it out. Count if won't work if there are decimals places unless using round. The formula above will look like: = Round(([Anticipated Graduation date]@row) - ([Pre-Service Class Start Date]@row)) / 7)
I really appreciate your time looking at this!!
Answers
-
Also, if I type the number on column Week in PS under the Pre-Service sheet, rather than a formula, it will take just fine when doing COUNTIF.
-
Try using
=COUNTIF({Pre-Service Range 4}, =7)
or =COUNTIF({Pre-Service Range 4}, @cell = 7)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks for replying Jeff! I tried it, but didn't work. What puzzle's me is that if I type the number on the reference column (Pre-Service) rather than having a formula, it'll take it just fine.
-
Also, if the result or total on the reference table (Pre-Service) is "0" it'll bring with formula, but if other number greater than "0" it won't.
-
What happens if you wrap the Week in PS formula in a VALUE function
=VALUE(([Anticipated Graduation date]@row - [Pre-Service Class Start Date]@row) / 7)
And then leave the quotes off in the COUNTIFS
=COUNTIFS({Pre-Service Range 4}, @cell = 7)
-
Finally figured it out. Count if won't work if there are decimals places unless using round. The formula above will look like: = Round(([Anticipated Graduation date]@row) - ([Pre-Service Class Start Date]@row)) / 7)
I really appreciate your time looking at this!!
-
I didn't think about that yet, but it does make sense that was the issue. It will work with decimals so long as you are searching for that. If you are searching for 7 and the cell contains 7.1, it won't match up and you'll get a zero count unless you actually search for 7.1 even if you have it set to not display decimals.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!