# Explore. Ask. Answer.

or Explore Discussions

# 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

• 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

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

• @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.

• 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.

• Fantastic! Glad we got it figured out.

Sign In or Register to comment.