Determining Count of Items in a column based on Specific Dates

Nick Stamatakis

As the title suggests, I am trying to determine the number of Active Projects, based on status, on a monthly basis.

I am not basing my logical expression based on a date criteria in a cell; I simply want to see:

• Active Projects (based on a status of: Not Started, In Progress, On Hold)
• Jan 01 to Jan 31 (as an example; would be doing this for every month of the year in separate cells)

This is what I have but I am missing the date element:

=COUNTIFS({PS - Project Status}, OR(@cell = "Not Started", @cell = "In Progress", @cell = "On Hold"))

Many thanks in advance!

Paul Newcome

This is how I would have things set up:

Sheet 1: Source Data

Sheet 2: Various metrics such as COUNTIFS and whatnot. This sheet also has the end of month formula and automation on it. The automation is (trigger) set to run daily (condition) when date column is today (action) copy row(s) to Sheet 3.

Sheet 3: This has the static data at the end of every month. From here you can use reports and/or additional metrics sheet(s) to collate this data and produce charts and whatnot.

Paul Newcome

You would use another range/criteria set looking at the date column and determining if it is between the two dates.

Nick Stamatakis

Thanks @Paul Newcome As I wrote this question, I realized that answer was obvious but it allowed me to focus my question:

I do monthly reporting at the end of the month to see how many X projects are active. Is there a way to create a formula to do a capture of a metric based on a snapshot in time (end of the month)? I tried to see if I can fiddle with the Today() function or Date() but I don't think I can do that... unless I am missing something of course....

Since I am reporting on mostly active projects, determining counts of these active projects at the end of the month is challenging because there is no End Date to leverage and the Start Date really doesn't play a factor in determining this magical number I am trying to capture (if that makes sense).

Paul Newcome

You would set up your metrics sheet with the formulas containing cross sheet references to pull for current month. You would include a date type column with a formula that outputs the last day of the current month. Then you would set up a copy row automation to copy the metrics over to another sheet when the date type column is "today" so that you can keep track of the historical data.

Nick Stamatakis

So I am slowly understanding. If I can reiterate what you are saying:

• In my source sheet (Portfolio Summary), I should add a column (hidden) that lists the last day of each month. Realistically, this will be one column with twelve cells underneath listing my target dates.
• I imagine I could theoretically have any sheet with a column with this information and can have a cross sheet reference to match the month by the row and compare against the final month; is that accurate?
• In the sheet where I want to capture my stats (Monthly Summary), I'd be creating a COUNTIFS formula, of course with the parameters I need, and another range/criteria pair whereby my range is the final date of the month and compare against a cell (I imagine in any sheet with a cell that has =TODAY() in it) to pull the count of the items I am looking for

So in theory, it might look something like this:

=COUNTIFS({Project Status}, OR(@cell = "Not Started", @cell = "In Progress", @cell = "On Hold"), {Monthly End Date}, {Last Day of Month})

{Project Status} - This is in my Portfolio Summary Sheet

{Monthly End Date} - I don't see why I can't have this in my final metric sheet

{Last Day of Month} - This can be a cell in any sheet (final metric sheet?) to compare against

I get the idea but I am unsure if I am developing the formula correctly to take into account the dates. Are you able to provide a sample/dummy formula that I can play with? Even if it was screenshots, that might let me wrap my head around this.

Thank you in advance sir; you are a gentleman and a scholar :-)

Paul Newcome

Not quite. You would not add anything to the source sheet. You would run your regular COUNTIFS formulas in the metrics sheet and add a date type column on the metrics sheet. In this column would be a formula to output the last day of the current month based on today's date.

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1

Then you would set up a copy row automation from the metrics sheet to another sheet based on this helper column in the metrics sheet being "today". That will copy your counts over on the last day of every month. Now you will have the historical record of how many were at a certain status at the end of each month to be able to chart or do with as you need.

Nick Stamatakis

Still getting a little lost here @Paul Newcome but we'll get there. Here's where I'm at:

• Not touching my source sheet (Portfolio Summary - Sheet 1); the data stays as is
• In my metrics sheet (Sheet 2), where I intend to list my COUNTIFS formulas to capture stats, has a date column added where one cell contains the formula you've shared with me. Right now, it will display: 02/29/24
• In the same metrics sheet (Sheet 2), I have 14 columns: 1 for the names of the stats I am capturing, 12 columns to capture each stat per month, 1 date column with only one cell with the formula you supplied to determine the last date of the current month
• Some of the stats in my metrics sheet (Sheet 2) are straightforward as there is a static date I can grab from Sheet 1 to get my count for the month
• The stats I have in Sheet 2 that are to capture Active Projects, Open Issues/Risks are what I am having difficulty leveraging your logic on this sheet.

It's Sheet 3 that you are suggesting that is confusing me; perhaps I am blending ideas from Sheets 2 and 3 that is causing my confusion. I suppose I am unsure on how to use that formula in one cell to capture counts for a month in Sheet 2:

• Am I adding something to my COUNTIFS formulas on the Active Projects that I am missing?
• Why do I need to run a copy row automation to Sheet 3 to get this count when I am trying to count everything in Sheet 2?

Apologies if I am not seeing your vision here; I think we're almost there. If you can share more of your wisdom for me, I think we can solve this problem :-).

As always, thank you 🙌

Paul Newcome

You don't use a date piece in the COUNTIFS (this just captures "current", and you don't need separate columns for each month unless you want to bring those back.

You need another sheet. A third sheet. The date formula I provided goes on every row that contains a metrics you want to capture monthly. You then set up a copy row automation from the metrics sheet to the third sheet to run when the date column containing the formula I provided is "today".

Nick Stamatakis

OK @Paul Newcome I think we're cooking with fire now. So, this is the deal on my end:

• Sheet 1: This is my Portfolio Summary where all my projects (with the parameters I am interested in) are located. Nothing was touched here; it's only used as a reference
• Sheet 2: This is my intermediary sheet where I have some formulas to get static items (e.g. completed projects based on an end date within a specific month) as well as where the Copy Row automation from Sheet 3 will be placed
• Sheet 3: This is the sheet where I've created a column with your formula to get the last date of the month. In addition to this, I created my sheet summary metrics to capture stats of active projects with a Project Status <> "Complete". As I was writing this, I realized it is better to have a column next to the desired stats with the same formula in my Metrics in the Sheet Summary (as that is what will be copied over, not the Metric in the Sheet Summary). In addition to this, I created an Automation on this sheet (with the below criteria) to get that snapshot of the active projects as of the last day of the month at 11PM.

Did I finally get it?

I will definitely report back next Thursday (March 01) to let you know if it worked properly and perhaps provide some feedback on what I see (versus my expectations) in order to make this automation be completely hands off. I feel like I could have a forth sheet (gasp!) where I place my copied cells over for the metrics/stats that are fluid. Once I see the pattern of how things are copied, I feel like I COULD have a cell reference to my Sheet 2 to auto-populate my desired 'final' cell for that specific fluid metric so I never have to transpose it with the additional keystrokes. The only real effort I'd have is to create this package of sheets annually (and that's no biggie at all).

Thinking out here but: how would you handle my stats where there are some projects that could have multiple service lines associated with them? That is, I have 10 projects total:

• Project 1 has Service Lines A and C
• Project 2 has Service Lines A and B
• Project 3 has Service Lines B and C
• Project 4 has Service Line A
• Project 5 has Service Line A
• Project 6 has Service Line B
• Project 7 has Service Line C
• Project 8 has Service Line C
• Project 9 has Service Line A
• Project 10 has Service Line A

Would you leave your stats as 10 total active projects with Service Lines A, B and C but there are:

• 6 with Service Line A
• 3 with Service Line B
• 4 with Service Line C

This might make someone think that there's actually 13 projects, not 10. Trying to foolproof my reporting to the 'less-than-intuitive' that would presume my math is wrong but in fact, it is reporting as expected.

I guess this question is more of an opinion rather than how do you do this?

Thank you, as always, Paul. You are a gentleman and a scholar :-)

Paul Newcome

This is how I would have things set up:

Sheet 1: Source Data

Sheet 2: Various metrics such as COUNTIFS and whatnot. This sheet also has the end of month formula and automation on it. The automation is (trigger) set to run daily (condition) when date column is today (action) copy row(s) to Sheet 3.

Sheet 3: This has the static data at the end of every month. From here you can use reports and/or additional metrics sheet(s) to collate this data and produce charts and whatnot.