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!

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 07/21/23

    @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
    TannerG ✭✭
    edited 07/24/23

    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
    JamesB ✭✭✭✭✭✭
    edited 07/24/23

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!