COUNTIF Returning "0"
Hi, I have two sheets, "Tracking" and "Summary". In the "Tracking" sheet, I have a column called Fiscal Year. This is a calculated column based on the date entered in the "Date of Incident" row. It works correctly, returns values of "2022" or "2023". Here is the formula:
=IF(AND([Date of Incident]@row >= DATE(2022, 2, 1), [Date of Incident]@row <= DATE(2023, 1, 31)), "2022", IF(AND([Date of Incident]@row >= DATE(2023, 2, 1), [Date of Incident]@row <= DATE(2024, 1, 31)), "2023", ""))
In my "Summary" sheet, I have a number of values pulling from the "Tracking" sheet. At the top of the summary sheet I have a cell in which I type in the Year (i.e. 2023). All of the formulas in the summary sheet reference this year [Column3]2 so that I can easily change just the year and get all updated values in the sheet (don't have to change each cell formula for the next fiscal year).
However, these formulas are all returning a "0" even though there are many cells in the original sheet which indicate "2023". Below is one example formula (basically counting the cells in the "Tracking" sheet Fiscal Year column which equal the number put in [Column3]2).
=COUNTIF({Accident Tracking Range 7}, =[Column3]2)
Assuming this has something to do with the calculation in the "Tracking" sheet and how it returns the value of "2023"?
Answers
-
@ctsammon remove the "=" sign before [Column3]2 in your formula
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Still getting "0" as the result
-
It has something to do with the number format in the "Tracking" sheet formula. If I change the number to text such as "Test", and then use "Test" in the summary sheet, then it counts correctly.
-
I figured it out. Because I was putting "2023" in quotation marks in the "Tracking" sheet, I think it was considering that as text, whereas the "Summary" sheet 2023 was a number. I removed the quotation marks from the tracking sheet and it works perfectly now.
-
Hi @ctsammon
I hope you're well and safe!
Excellent! Glad you got it working!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!