Averageif for specific Project Phase Duration

tim.curtin

I need to show the average of an {Initiation Duration} by each month. So average for Jan, Feb, March, etc.

Columns

Initiation Start (Date Column)

Initiation End (Date Column)

Initiation Duration (text Column w/ formula)

I have a formula on my source sheet or "portfolio" for Initiation Duration which is:

=NETWORKDAYS([Initiation Start]@row, [Initiation End]@row) / 12

Taking that data as a reference formula I need to build in to a metric sheet showing monthly results.

I believe this is the end of my formula.

{Initiation End}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023

No sure how to lay out the AVERAGEIF formula and my combos have been unsuccessful.

Any help would be awesome!

Thank you,

Tim

Jeff Reisman

@tim.curtin The first thing I recommend for anyone needing to divide counts and such by month is to create a MonthNumber lookup, either in their metric sheet or in a standalone sheet. This lookup equates 1 with January, 2 for February, etc. If your metrics sheet lists the months with a column next to that for your averages, then just add a hidden MonthNum column and populate it with 1 through 12.

You are looking to average these by the month in Initiation End, right? But you also want to specify the year. The tough part is that there's no AVERAGEIFS function that allows you to specify multiple criteria. So we have to sum the values and divide by the row count.

Try this:

=SUMIFS({Initiation Duration}, {Initiation Duration}, ISNUMBER(@cell), {Initiation End}, ISDATE(@cell), {Initiation End}, (MONTH(@cell) = MonthNum@row), {Initiation End}, (YEAR(@cell) = 2023)) / COUNTIFS({Initiation Duration}, ISNUMBER(@cell), {Initiation End}, ISDATE(@cell), {Initiation End}, (MONTH(@cell) = MonthNum@row), {Initiation End}, (YEAR(@cell) = 2023))

The logic: Add up the Initiation Duration column where Initiation Duration is a number, Initiation End is a date value, and where the Month of the Initiation End date is the MonthNum from this row, and the year of that date is 2023... then divide that sum by the count of rows where Initiation Duration is a number value, Initiation End is a date value, and where the Month of the Initiation End date is the MonthNum from this row, and the year of that date is 2023.

*Note - Really shouldn't need IFERROR here since we're telling the SUMIFS and COUNTIFS functions to only do their thing on rows where you have a number value in Initiation Duration, and a date value in Initiation End.