Countifs Formula for Current Month and Year

04/08/21
Accepted

I am trying to count the number of documents with closed status for the current month/year. I am having difficulty with the formula. Here is what I have so far:

=COUNTIFS(([Closure Actual]:[Closure Actual], IF(ISDATE(@cell), MONTH(@cell)=MONTH(TODAY()))), [Closure Actual]:[Closure Actual], IF(ISDATE(@cell), YEAR(@cell)=YEAR(TODAY())), [Stage Status]:[Stage Status], "Closed")

Can someone help.

Best Answer

  • Heather DHeather D ✭✭✭✭✭
    Accepted Answer

    That's a possibility - I'm not sure. Try changing your year/month formulas to this:

    For the Year column:

    =if(isblank([closure actual]@row),"",YEAR([closure actual]@row))

    For the Month column:

    =if(isblank([closure actual]@row),"",MONTH([closure actual]@row))


    That should do away with the errors, and we can see if that fixes the formula.

Answers

  • Heather DHeather D ✭✭✭✭✭

    Hi @Danielle Trantham ,


    You may want to employ some helper columns for this. These columns can be hidden once you've made them column formulas.

    For the Year column:

    =YEAR([closure actual]@row)

    For the Month column:

    =MONTH([closure actual]@row)

    Then you could do this:

    =COUNTIFS(year:year,@cell=YEAR(TODAY()),month:month,@cell=MONTH(TODAY())


    Let me know if it works!


    Best,

    Heather

  • The additional columns worked, however, there is one other criteria I need to add to the Countif formula which is "Closed" status. I have tried this formula and it is giving an error.

    =COUNTIFS([Stage Status]:[Stage Status], "Closed", Year:Year, @cell = YEAR(TODAY()), Month:Month, @cell = MONTH(TODAY()))

  • Heather DHeather D ✭✭✭✭✭

    @Danielle Trantham , you're close! add @cell= in front of "Closed" and it should do the trick:

    =COUNTIFS([Stage Status]:[Stage Status], @cell="Closed", Year:Year, @cell = YEAR(TODAY()), Month:Month, @cell = MONTH(TODAY()))

  • Still giving invalid data type error. Does this have to do with the additional columns displaying #invalid for items that do not have a closure date recorded? See picture.


  • Heather DHeather D ✭✭✭✭✭
    Accepted Answer

    That's a possibility - I'm not sure. Try changing your year/month formulas to this:

    For the Year column:

    =if(isblank([closure actual]@row),"",YEAR([closure actual]@row))

    For the Month column:

    =if(isblank([closure actual]@row),"",MONTH([closure actual]@row))


    That should do away with the errors, and we can see if that fixes the formula.

  • Thank you so much. That worked.

  • Heather DHeather D ✭✭✭✭✭

    Fantastic! Glad we got it figured out.

Sign In or Register to comment.