Why isn't my COUNTIF formula updating?

aprkns

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")

Deric

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.

aprkns

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.

Danielle Arteaga

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))

Danielle Arteaga

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

aprkns

Thank you! I'll try that. Fingers crossed.

Deric

@aprkns

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")

aprkns

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!