Why isn't my COUNTIF formula updating?
I am using the COUNTIF formula to keep track of quarterly reporting status for clients. For example, I need to track how many clients have a "failure to report" status. The row that I am pulling the data from is a dropdown selection - which I have never had issues with before now. When I plug in the formula, it registers as zero even when there are counts for the status (see image below). This is the formula I am using: =COUNTIF([Row Title]:[Row Title], "status option")
Answers
-
Are you looking at data across rows or in a specific column? If you are looking across rows, you need to reference the column and row, e.g., [column1]2:[column6]2.
If you are talking about columns and "[row title]" means "[column title]", then think you have an incorrect search value for countif. Is your "status option" supposed to be "failure to report"? Or is there more than one possible search value?
If you give more specifics, I might be able to offer more useful help.
-
Oops, yes I meant column instead of row.
I use different search values. So, Failure to report for Q1 would have the following options: "Failure to report January," "Failure to report February," and "Failure to report March." I use a formula for each value - below is an example.
Example: =COUNTIF([Q1_2023_Agency_Status]:[Q1_2023_Agency_Status], "Failure to report January")
The issue shouldn't be the column title - it works with other values. The underscores are due to the long column title.
It seems like the sheet summary feature is choosing not to populate the correct counts for random formulas. I can't find a reason why it would be formula-specific - all of the formulas are the same, except for the status option.
-
Hi, there. It's hard to answer your question here without more information about your data structure.
In the column called Row Title, is one of the dropdown options "status option"? If it is not, then it makes sense that your formula returns 0. If your dropdown option is "failure to report," then that is what should be in the quotation marks.
I'm also wondering how you are aggregating your results for each time period (e.g., January, February, March). Is that happening via a formula in a different column? If not, then your COUNTIF formula would need a date parameter in it as well. Something like this:
=COUNTIFS([Row Title]:[Row Title], "failure to report", [status date]:[status date], >=DATE(2023, 2, 1), [status date]:[status date], <=DATE(2023, 2, 29))
-
There is nothing syntactically wrong with your formula, so it should work on sheet or in the Sheet Summary. You might try closing your browser, clearing your cache, then going back into the sheet to see if the formulas update on your Sheet Summary. That has worked for me in the past. (Also, sometimes going into your sheet via a different web browser can fix it.) Otherwise, it looks like something that would require opening a support case with Smartsheet: https://help.smartsheet.com/contact/smartsheetapp
-
Thank you! I'll try that. Fingers crossed.
-
Try copying the formulas into cells in the sheet and testing to see if they work the way you expect them to. That will help rule out of it is a sheet summary issue.
If you find that there are specific formulas that still don't work in the sheet, append an "!" to the beginning of the formulas and carefully compare them for discrepancies and spelling error.
!=COUNTIF([Q1_2023_Agency_Status]:[Q1_2023_Agency_Status], "Failure to report January")
!=COUNTIF([Q1_2023_Agency_Status]:[Q1_2023_Agency_Status], "Failure to report February")
!=COUNTIF([Q1_2023_Agency_Status]:[Q1_2023_Agency_Status], "Failure to report March")
Also check your drop down list or formulas (whatever is generating the "failure to report" message) to ensure that there isn't a spelling error.
I would also try entering other formulas using the same search terms to see what happens, e.g., =IF(CONTAINS("failure to report January", [Q1_2023_Agency_Status]:[Q1_2023_Agency_StatusL), "TRUE", "FALSE")
-
Thank you. I tried your suggestions, but nothing shows that anything is misspelled or incorrect. I'm wondering if there is a bug or if the sheet I am using just has too much data? I opened a support case, so we'll see. Thank you again for help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 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!