Taking average instead of SUMPRODUCT

**john_london** · 07-30-2012, 12:11 PM

Hi Guys -

I've used this formula on my spreadsheet:

Please Login or Register  to view this content.

... and it works great, summing cells only for a selected month and year.

However, if I wished instead to take the average of cells for a month and year, instead of summing them, how would this be done?

Many thanks,

John

**MarvinP** · 07-30-2012, 12:21 PM

Hi john,

How about dividing the sum by the count? If you showed us a sample workbook it might be easier to explain. The general idea is to do your formula above and then divide that result by CountIfs( <the same criteria used in the SumProduct>).

I hope that makes sense.

I'm a Pivot Table guy and thing if your data is in a table you could do a Pivot Table with a filter and easily get Average instead of Sum.

You can attach a sample workbook by clicking n "Go Advanced" and then the Paper Clip Icon above the advanced message area. Then I could see if a Pivot Table answer is easy or not.

**ChemistB** · 07-30-2012, 12:22 PM

Try

Formula:

Please Login or Register  to view this content.

Does that work for you?
With Excel 2007, you could also use AVERAGEIFS if you don't need to be compatable with 2003

**day92** · 07-30-2012, 12:40 PM

Or you can use an array with a shorter formula..

=AVERAGE(IF((YEAR(Sheet2!$A3:$A72)=$G$7)*(MONTH(Sheet2!$A3:$A72)=$J$6),Sheet2!$F3:$F72))

Confirm with Ctrl, shift enter

**daddylonglegs** · 07-30-2012, 01:04 PM

As you have Excel 2007 you can use AVERAGEIFS function which doesn't require "array entry", i.e.

=AVERAGEIFS(Sheet2!$F3:$F72,Sheet2!$A3:$A72,">="&DATE($G$7,$J$6,1),Sheet2!$A3:$A72,"<"& DATE($G$7,$J$6+1,1))