COUNTIFS for Jira data between date range

TannerG

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!

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