Nested Formula (PRODUCT & OFFSET)

[alamagan]

I'm having difficult with the following formula in ARRAY formula using Product & OFFSET nested formula:
Multiply A*B for all items (1-5) and add all together. Ignore blank cells.
This formula may be simple for some of you, but I can't figure this one out. Please help


ITEM A B
1 4 5 20
2 3 2 6
3 2 4 8
4 0
5 0
Total 34

[rcm]

Please Login or Register  to view this content.

[alamagan]

Thanks,

The formula that you provided works if there is no blank cells in the range. However, I have dynamic range that sometime have fields that contain no data and your formula returned zero value. It assumes the blank cells has zero value.

[rcm]

sumproduct(a:a*b:b*(a:a<>"")*(b:b<>""))

[daddylonglegs]

SUMPRODUCT doesn't need * to multiply the arrays, just use a comma and blanks will be ignored

=SUMPRODUCT(A1:A5,B1:B5)

but in any case if rcm's formula treats blanks as zeroes won't the result still be the same?

[Pepe Le Mokko]

Thanks,

The formula that you provided works if there is no blank cells in the range. However, I have dynamic range that sometime have fields that contain no data and your formula returned zero value. It assumes the blank cells has zero value.

Could you provide an example please? SUMPRODUCT will work whether there are empty cells or not

[alamagan]

Thanks all.
SumProduct works with blank cells. my problem was something else. Thanks again