The Average Number of Monthly Projects
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?
Best Answers

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(................), ......................)

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 nondate values within the range will not skew the incoming data.
Answers

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(................), ......................)

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?

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 nondate values within the range will not skew the incoming data.

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

@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,.....

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

@Jason Egles Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!