Conditional Multiply and Sum
 

I have a quantity column of values (col C) then values in E, F, G H & I.
Col J is the total of values in E-I.
Col K is the quantity in col C multiplied by the total in col J.
Since some rows do not contain values, the formulae in cols J and K are
conditional
eg:
=IF(OR(NOT(COUNT(RC[-7]))),"",SUM(RC[-5]:RC[-1])) for col J, and
=IF(OR(NOT(COUNT(RC[-8])),NOT(COUNT(RC[-1]))),"",
ROUND(IF(ISNUMBER(SEARCH("item",RC[-8])),1,ROUND(RC[-8],2))*IF(ISNUMBER(SEARCH("item",RC[-1])),1,ROUND(RC[-1],2)),2)) for col K.

I'm trying to do a similar exercise, but this time cycling down rather than
across.

In essence, I need to multiply say r3c3 by r3c5 and add the result to say
r1000c5, then r4c3 by r4c5 etc, ie getting a total for col E, then ditto for
cols F to I .

How best to do this please?

Regards,
Stuart.

Conditional Multiply and Sum
 

Hi,

Put in E1000 this formula =SUMPRODUCT($C$2:$C$999,E2:E999) and then drag it
to right side F1000 onwards.

Regards
Vijay

"Stuart" wrote:

I have a quantity column of values (col C) then values in E, F, G H & I.
Col J is the total of values in E-I.
Col K is the quantity in col C multiplied by the total in col J.
Since some rows do not contain values, the formulae in cols J and K are
conditional
eg:
=IF(OR(NOT(COUNT(RC[-7]))),"",SUM(RC[-5]:RC[-1])) for col J, and
=IF(OR(NOT(COUNT(RC[-8])),NOT(COUNT(RC[-1]))),"",
ROUND(IF(ISNUMBER(SEARCH("item",RC[-8])),1,ROUND(RC[-8],2))*IF(ISNUMBER(SEARCH("item",RC[-1])),1,ROUND(RC[-1],2)),2)) for col K.

I'm trying to do a similar exercise, but this time cycling down rather than
across.

In essence, I need to multiply say r3c3 by r3c5 and add the result to say
r1000c5, then r4c3 by r4c5 etc, ie getting a total for col E, then ditto for
cols F to I .

How best to do this please?

Regards,
Stuart.

Conditional Multiply and Sum
 

Hi Vijay,

Thanks, but I get an error up when I try to exit the cell, having pasted
your formula.

Regards,
Stuart.