COUNTIFS with Multiple Date Fields

Jennifer Lewkowicz

Hi all,

I'm having some issues with a formula concept. My management wants a year to date view on various Phase Status within a Project Portfolio. I am trying to figure out, with dynamic figures how to get YTD on projects and settled on using all the various date fields as maybe an OR Function. I'm not sure if this is the right approach.

The idea here is that it would count if Planning Phase Date or Baseline Start, or Baseline LBE, or Actual Start, or Actual LBE/GO Live have a date in 2021. But I don't want it to count more than once.

I tried this:

=COUNTIFS({CREReporting}, 0, {CREPhase}, [Metric Name]@row, OR({CREPlanningDate}, >=DATE(2021, 1, 1), {CREBaseStart}, >=DATE(2021, 1, 1)))

I get invalid data type.

Any ideas?

Some screen caps below as I work this out.

Mike Wilday

Based on the fact you are looking at the counts for either of two columns. I would add results together with simple math like this.

=COUNTIFS({CREReporting}, 0, {CREPhase}, [Metric Name]@row, {CREPlanningDate}, >=DATE(2021, 1, 1)) + COUNTIFS({CREReporting}, 0, {CREPhase}, [Metric Name]@row, {CREBaseStart}, >=DATE(2021, 1, 1))

Does that work for you? The challenge is that it might double count some if the CREBaseStart and the Planning date is in the same date count.

You might also use a helper column in your source sheet to count all the rows that meet that requirement. Add a new column and do this... Replace the column headers if I guessed the wrong ones.

=IF(OR([Planned Phase Begin Date]@row >=DATE(2021, 1, 1), [Baseline Start Date]@row >=DATE(2021, 1, 1), 1, 0)

Then you could do a cross sheet reference counting all rows or summing all rows with a 1 in the helper column.

Would that work better?

Mike Wilday

Based on the fact you are looking at the counts for either of two columns. I would add results together with simple math like this.

=COUNTIFS({CREReporting}, 0, {CREPhase}, [Metric Name]@row, {CREPlanningDate}, >=DATE(2021, 1, 1)) + COUNTIFS({CREReporting}, 0, {CREPhase}, [Metric Name]@row, {CREBaseStart}, >=DATE(2021, 1, 1))

Does that work for you? The challenge is that it might double count some if the CREBaseStart and the Planning date is in the same date count.

You might also use a helper column in your source sheet to count all the rows that meet that requirement. Add a new column and do this... Replace the column headers if I guessed the wrong ones.

=IF(OR([Planned Phase Begin Date]@row >=DATE(2021, 1, 1), [Baseline Start Date]@row >=DATE(2021, 1, 1), 1, 0)

Then you could do a cross sheet reference counting all rows or summing all rows with a 1 in the helper column.

Would that work better?