Countifs for items completed YTD by quarter?

Jennifer Sizelove

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?

Paul Newcome

Couldn't you just count how many are "Q1"?

=COUNTIFS([Closing Q]:[Closing Q], "Q1")

Paul Newcome

Couldn't you just count how many are "Q1"?

=COUNTIFS([Closing Q]:[Closing Q], "Q1")

Jennifer Sizelove

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.

Jennifer Sizelove

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

Paul Newcome

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

Jennifer Sizelove

What does the ISDATE(@cell) function do in the formula above?

Paul Newcome

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?

Jennifer Sizelove

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 !

Paul Newcome

Happy to help. 👍️