Countifs Formula for Current Month and Year

Danielle Trantham

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.

Heather Duff

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.

Heather Duff

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

Danielle Trantham

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 Duff

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

Danielle Trantham

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 Duff

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.

Danielle Trantham

Thank you so much. That worked.

Heather Duff

Fantastic! Glad we got it figured out.