Formula not Calculating the Correct Number

Hello,

I have a formula that counts notifications that are tracked on another smartsheet each month. The formula is:

=COUNTIFS({Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row , {Impacted Department}, <>"XX")

The results from this formula are no longer calculating correctly. For example, in February, when you apply a filter to the parent smartsheet, you get 20 results instead of 6.

Is there something wrong with my filter that I didn't notice before?

Find more posts tagged with

Sheets

Formulas

Accepted answers

kss5229

It looks like the formula is not counting cells where "Impacted Department" is blank. I've updated my formula to this and it seems to be working:

=COUNTIFS({Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row , {Impacted Department}, <>"XXXX") + COUNTIFS({Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row , {Impacted Department}, "")

All comments

Janae G.

Hello @kss5229 ! Nothing stands out to me as an obvious error here. Some things you could double check:

If Impacted Department includes numbers- make sure the range is all numbers (no leading 0s) or all text. Things can get miscounted if the range is a mix of numbers and text.
Make sure your cross-sheet references are referencing the whole column
Make sure the dates in Start Date and End Date are recognized as actual dates. Sometimes the filter will work but formulas are finnicky with dates. Check by hovering over a cell to see if it gives you e.g. "Tuesday, February 24, 2026".
- Also check that both date columns are Date column types

COUNTIFS can be finnicky sometimes- hopefully one of these or something similar is the culprit.

Hope this helps!

Paul Newcome

Try setting your filter to be exactly how you you have your formula. The "is between" filter does not always include the start and end dates.

kss5229

Thanks for these suggestions! I've verified all of the following:

Impacted Department does not include numbers
Cross-sheet references are referencing the whole column
The dates in Start Date and End Date are recognized as actual dates.
- Both date columns are Date column types
I've set the filter to be exactly as the formula (see screenshot below).

I'm still getting a much higher count from the filter than the formula….

Paul Newcome

The next part then would be to break this down into pieces. Try the formula with just the start date and the sheet filter with just the start date. THen again with the end date and again with the department.

kss5229

It looks like the formula is not counting cells where "Impacted Department" is blank. I've updated my formula to this and it seems to be working:

Paul Newcome

Glad you were able to get that sorted. You can actually simplify a bit using the OR function and only a single COUNTIFS.

=COUNTIFS({Date of Notification}, AND(@cell >=[Start Date]@row, @cell <= [End Date]@row), {Impacted Department}, OR(@cell <> "XXXX", @cell = ""))

kss5229

I haven't figured out why the that formula was not counting blanks. Other columns that have been laid out similarly don't show a difference between the two counts:

A Similar Formula:
=COUNTIFS({CC Level}, CONTAINS("Level 1", @cell ), {Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row , {Impacted Department}, <>"XXXX")

Returns same value as this formula:
=COUNTIFS({CC Level}, CONTAINS("Level 1", @cell ), {Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row , {Impacted Department}, <>"XXX") + COUNTIFS({CC Level}, CONTAINS("Level 1", @cell ), {Date of Notification}, >=[Start Date]@row , {Date of Notification}, <=[End Date]@row , {Impacted Department}, "")

Paul Newcome

Blanks can sometimes be a little wonky. Generally speaking, I usually specify to include blanks when I want them included just because of this.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.