COUNTIFS for Jira data between date range

Hello!

Currently I have a Jira connector that pulls in Jira ticket data and sorts it by ticket topic (column 1), status(Column 2), and date opened. This sheet has a years worth of data separated by date opened.

I know how to use COUNTIFS to find the Open and Closed Jiras by topic (eg - =COUNTIFS({Reference Sheet Name}, "Closed", {Reference Sheet Name}, Topics@row).

I am trying to add to this formula to only pull in Jira closed ticket information for the month of January 2023.

Any help would be appreciated!

Find more posts tagged with

Accepted answers

All comments

JamesB

@TannerG

This will do a countifs between two dates. After 12/31/2022 and before 2/1/2023.

=COUNTIFS([Closed Date]:[Closed Date], >DATE(2022, 12, 31), [Closed Date]:[Closed Date], <DATE(2023, 2, 1))

TannerG

Hey @JamesB

Thanks for that reply! So I see that returns the total number of Jira tickets within that date range, is there a way to add a COUNTIFS within this formula to return only CLOSED Jira within that target date range?

So its a COUNTIFS of Closed Jiras AND a COUNTIFS of Jiras within a specific date range.

=COUNTIFS({Reference Sheet Name}, "Closed", {Reference Sheet Name}, Topics@row) AND COUNTIFS([Closed Date]:[Closed Date], >DATE(2022, 12, 31), [Closed Date]:[Closed Date], <DATE(2023, 2, 1)) ?

I dont think I can use the same function within the formula twice, correct?

JamesB

@TannerG

You should not need an AND statement, just additional criterion in your original formula.

=COUNTIFS({Reference Sheet Name}, "Closed", ({Reference Sheet Name}, >DATE(2022, 12, 31), {Reference Sheet Name}, <DATE(2023, 2, 1),{Reference Sheet Name},Topics@row)

Paul Newcome

I personally have found months are easier to manage when you reference the month and year directly instead of hard-coding in dates.

=COUNTIFS({Reference Sheet Name}, "Closed", {Reference Sheet Name}, Topics@row, {Closed Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

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.