Countifs for items completed YTD by quarter?
Hi everyone, first time poster here.
I need a formula that will count Buy closings YTD by quarter.
The best guess I have after reading other posts is:
=COUNTIFS([Buy close date]:[Buy close date], > DATE(2021,01,01), [Buy close date]:[Buy close date] < TODAY, [Closing Q]:[Closing Q], Q1)
It's not working but I can't quite piece together where I'm going wrong. Any tips or tricks?
Best Answer
-
Couldn't you just count how many are "Q1"?
=COUNTIFS([Closing Q]:[Closing Q], "Q1")
Answers
-
Couldn't you just count how many are "Q1"?
=COUNTIFS([Closing Q]:[Closing Q], "Q1")
-
Unfortunately not. I need a metric for how many closings are already completed within the current quarter, not including the upcoming closings in the current quarter.
-
...unless I left the Closing Q column blank until the closing was complete. That seems a lot simpler and would do the trick, wouldn't it? Thanks for getting me to think out of the box!
-
If you wanted to leave the quarter populated, you could adjust the COUNTIFS to
=COUNTIFS([Closing Q]:[Closing Q], "Q1", [Buy Close Date]:[Buy Close Date], ISDATE(@cell))
-
What does the ISDATE(@cell) function do in the formula above?
-
I just realized after taking another look at your screenshot that it won't work the way you are wanting it to. My apologies. Disregard that last one with the ISDATE function.
Are you currently manually populating the Quarter Column, or are you using a formula?
-
All good, the first suggestion you made did the trick. I just won't fill in the Closing Q column until each individual transaction row has actually closed. Thanks so much @Paul Newcome !
-
Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
