sumproduct
 

I have been successfully using a sumproduct formula to add cells from an
array based on criteria of the header rows and columns - something like:

SUMPRODUCT((Actuals!$C$10:$C$86=A8)*(Actuals!$F$5: $EZ$5=$E$2)*(Actuals!$F$4:$EZ$4="Restrictable")

I would now like to sum, not the values in the array directly, but the value
multiplied by a percentage held in another header row (which would be
different for each column). I tried:

=SUMPRODUCT((Actuals!$C$10:$C$86=A8)*(Actuals!$F$5 :$EZ$5=$E$2)*(Actuals!$F$4:$EZ$4="Restrictable")*( Actuals!$F$10:$EZ$86)*(Actuals!$F$3:$EZ$3))

Where the last part of the formula contains row with the percentages.

Can you help?

Hi Paul,

Does this work?

=SUMPRODUCT((Actuals!$C$10:$C$86=A8)*(Actuals!$F$5 :$EZ$5=$E$2)*(Actuals!$F$4:$EZ$4="Restrictable")*( Actuals!$F$10:$EZ$86),Actuals!$F$3:$EZ$3)

Cheers,
Jon

"Paul Clough" wrote:

I have been successfully using a sumproduct formula to add cells from an
array based on criteria of the header rows and columns - something like:

SUMPRODUCT((Actuals!$C$10:$C$86=A8)*(Actuals!$F$5: $EZ$5=$E$2)*(Actuals!$F$4:$EZ$4="Restrictable")

I would now like to sum, not the values in the array directly, but the value
multiplied by a percentage held in another header row (which would be
different for each column). I tried:

=SUMPRODUCT((Actuals!$C$10:$C$86=A8)*(Actuals!$F$5 :$EZ$5=$E$2)*(Actuals!$F$4:$EZ$4="Restrictable")*( Actuals!$F$10:$EZ$86)*(Actuals!$F$3:$EZ$3))

Where the last part of the formula contains row with the percentages.

Can you help?