SUMPRODUCT NON-CONTINOUS CELL

[Harsh Bahal]

I want ot enter a formula in cell A20 which should be the sumproduct of range
A1 to A19 & of coloumn B1 to B19 but to exclude some cells from both
coloumns, conditionality being if there are no figures in any of the cell
(cell value being 0). Thus if cell A5=0, to ignore both cell A5 & B5.
pL help.
Thanks in anticipation

 

[Mike H]

Try

=SUMPRODUCT((A1:A19<>0)*(A1:A19)*(B1:B19))

Mike

 

[driller]

Harsh,

if my recollection is correct, u can try this while u imagine ignoring the
rows with 0's in it

A20 =sumproduct(A1:A19,B1:B19)

sidenote:

Thus if cell A5=0, to ignore *both* cell A5 & B5.

0 x anynumber = 0 , thus the resulting SUM of the *valued* PRODUCTS from the
pair of range (A1:B19) will be correct on A20.