Array formula for SUMPRODUCT

[Tim Lindsley]

Hello,

Could someone please tell me how to convert the following SUM formula
to a SUMPRODUCT array formula?

=SUM(AP3*(AP4/AQ4),AO3*(AO4/AQ4),AN3*(AN4/AQ4),AM3*(AM4/AQ4),AL3*(AL4/AQ4),Y3*(Y4/AQ4),O3*(O4/AQ4))

Thanks!

Tim

 

[T. Valko]

Try this:

=SUMPRODUCT(AL3:AP3,AL4:AP4/AQ4)+SUM(Y3*(Y4/AQ4),O3*(O4/AQ4))

Biff

 

[T. Valko]

If any division results in #DIV/0! the formula result is #DIV/0!.

The original formula didn't have anything to account for that so I didn't
either. We don't know the conditions under which this formula operates.
That's what we have to deal with when we make suggestions!

Biff

 

[Martin Fishlock]

It is possible to further refine the solution as AQ4 is common to all it can
be taken out and divided over the total and the last two sums are not really
needed:

=(SUMPRODUCT(AL3:AP3,AL4:AP4)+Y3*Y4+O3*O4)/AQ4

But basically the same.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

If any division results in #DIV/0! the formula result is #DIV/0!.

The original formula didn't have anything to account for that so I didn't
either. We don't know the conditions under which this formula operates.
That's what we have to deal with when we make suggestions!

Biff

 

[T. Valko]

the last two sums are not really needed:

I used SUM just to make it easier to follow the precedence.

AQ4 is common to all it can be taken out and divided over the total

Good point!

Biff

It is possible to further refine the solution as AQ4 is common to all it
can
be taken out and divided over the total and the last two sums are not
really
needed:

=(SUMPRODUCT(AL3:AP3,AL4:AP4)+Y3*Y4+O3*O4)/AQ4

But basically the same.