how do I sum the product of two non-adjacent rows?

[Zarathrustra]

I wish to do a summation of the product of two sets of parallel cells, said cells separated by other rows.

The seemingly simple solution of sum of the start pair of cells - the end pair of cells, or sumproduct, just turns the function into an array.

Am I perhaps asking the impossible, or is there some way to do this?

[Limor_OP]

You didnt explain what you are trying to do in your file.

[Cemotivational]

I didn't quite get you but adding condition to a SUMPRODUCT is perfectly possible (no array formula needed). Double negatives convert true/false to 1/0, which can be then used in the sumproduct.

In the attached example I am sumproduct-ing 2 columns for all days but Sundays.

Please Login or Register  to view this content.

[maniacb]

try adding using straight =c3+i3, instead of using the sum formula, I hope I understood your question.

[Zarathrustra]

Apologies to you all. I was too vague/cryptic.

What I wish to do is sum the product of each cell in rows 3 to 14 by those in row24 along each row in turn and sum those for each row, but whilst I can an individual product (such as =B16*B24 =-0.05281886), I cannot find a way to both do that with each pair from Col.B to Col.N and sum that lot.

I eventually wish to do this with all the rows, putting the results in Col.P

I hope that clarifies the problem.

I post an updated spreadsheet.

[Gregb11]

I'm not sure if I'm following you or not, but what about putting this formula in P3 and copying it down:

=SUMPRODUCT(B3:N3,$B$24:$N$24)

[Zarathrustra]

Many thanks for this solution, and staying with me in my stumbling presentation.

This way of using the function - which may well be how it was always supposed to work - had eluded me.