Countifs Formula for Current Month and Year
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
-
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()))
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!