Countifs calculation giving wrong number

I'm trying to make a grid that counts how many tickets were open on any given date. In my grid I have a column for the dates I want to put in and then columns for how many tickets were open on that specific date. The formula I'm using to calculate open tickets on that date is:

=COUNTIFS({Ticket List - Created}, <Date@row, {Ticket List - Closed}, >=Date@row)

In my mind this should count all rows in the grid "Ticket List" that have a created date before the date I put in, and a Closed date on or after the date I put in.

The formula ends up giving me 213, but when I go to my source sheet to verify I set up the filters to match the formula I only get 40 results. I've looked through Ticket List and verified that the filter search is accurate. I just filtered Ticket List for Closed greater than the date in my other grid and that strangely resulted in 213. It's almost like it's only counting the second range/criteria and ignoring the first.

Does anyone see what I'm doing wrong?

Find more posts tagged with

Accepted answers

All comments

Michelle Choate 2

Well - For it to be closed, it would have had to be created before the closed date, so 213 looks correct. Can you take a photo of your filter only giving you 40?

BrianS

https://community.smartsheet.com/discussion/comment/464108#Comment_464108

I meant that if I only set a filter for Closed date after 8/5/24 then I get 213 results, which would include tickets created after 8/5/24. My countifs should only be counting tickets created before 8/5/24 and closed after 8/5/24. I set my filter for that and got this:

I just uploaded a new file to update my Ticket List and now the formula works perfectly. I've noticed a few times where something doesn't update for a significant amount of time, I'm using Smartsheets since it has some extra functions over excel and my company uses it for some other stuff, but it's really frustrating that this sort of stuff doesn't work consistently.

Michelle Choate 2

That is super strange! I have noticed that some of my formulas pull wrong numbers or cell links pull in no info until I save and refresh sometimes. Sorry that happened to you but I am so glad that it is working now!

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.