SUMPRODUCT to average

[AJPendragon]

I have a SUMPRODUCT for numbered data, but also require it for % data. My
SUMPRODUCT is:

=SUMPRODUCT(--(Sheet2!B4:B573=A6),(Sheet2!C4:C573))

This provides a total % figure of 475%, but I want the average of the 5
figures selected (it could be as many as 1-5 figures) - is this possible with
the SUMPRODUCT formula.

Thanks for looking

 

[Pete_UK]

An average of 5 percentage values doesn't mean very much unless the
totals for those five values are the same size. You could divide your
formula by a count of those values that met the criteria in A6 (i.e.
by COUNTIF(Sheet2!B4:B573,A6) ), but this wouldn't mean very much.
Perhaps you could explain more about what you want to achieve ...

Pete

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(Sheet2!B4:B573=A6,Sheet2!C4:C573))

Format as %

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)