The Average Number of Monthly Projects

Annie Lindenberg

I want to be able to have a formula that pulls the average number of projects we complete monthly for our sheet summary. I have a date column for "Date Completed". Can anyone help with what the formula would look like?

Paul Newcome

First you will need to gather the counts for each month. Something for January 2020 would look like...

=COUNTIFS([Date Completed]:[Date Completed], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))

Then you would need to duplicate this for each month. From there you would drop each one into a AVG function...

=AVG(COUNTIFS(................), COUNTIFS(................), COUNTIFS(................), ......................)

Paul Newcome

If there is a cell within the range that does not have a date in it such as a blank cell or text, then there will be no data for the YEAR or MONTH function which will throw an error. So we use an IFERROR to turn that error into a zero which obviously does not equal 1 or 2020 or whatever other year or month number you are searching for. This allows the formula to continue running and ensures non-date values within the range will not skew the incoming data.

Paul Newcome

First you will need to gather the counts for each month. Something for January 2020 would look like...

=COUNTIFS([Date Completed]:[Date Completed], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2020))

Then you would need to duplicate this for each month. From there you would drop each one into a AVG function...

=AVG(COUNTIFS(................), COUNTIFS(................), COUNTIFS(................), ......................)

Annie Lindenberg

Thank you, I was able to get this to work! I'm still a little bit confused by the use of the IFERROR function, would you be able to explain why the 0 needs to be there?

Paul Newcome

If there is a cell within the range that does not have a date in it such as a blank cell or text, then there will be no data for the YEAR or MONTH function which will throw an error. So we use an IFERROR to turn that error into a zero which obviously does not equal 1 or 2020 or whatever other year or month number you are searching for. This allows the formula to continue running and ensures non-date values within the range will not skew the incoming data.

Jason Egles

@Paul Newcome

Hi Paul,

Is there a modification that can be made to that same type of formula above to enable it to repeat for each consecutive month by dragging?

example:

.....(MONTH(@cell), 0) = 1,.....

.....(MONTH(@cell), 0) = 2,.....

.....(MONTH(@cell), 0) = 3,.....

Even when I have two rows above in the same pattern, it doesn't repeat.

Thanks, Jason

Paul Newcome

@Jason Egles Enter the number into a separate column and then use a cell reference.

Formula Column...............Month Number

..............f........................................1

..............f........................................2

..............f........................................3

Then the formula would be adjusted to say:

.....(MONTH(@cell), 0) = [Month Number]@row,.....

Jason Egles

Awesome! That's exactly what I needed. Thanks a lot @Paul Newcome

Paul Newcome

@Jason Egles Happy to help. 👍️