IF AND problem

[Amanda D]

I am trying to return a sum using IF AND

My obejective is IF E9 = 3 AND ISNUMBER in Z9 then take F9 multipled by BE9,
G9 multiplied by BF9, H9 multiplied by BG9 and so on.

F9, G9 & H9 will be various amounts for different products.

I have percentages in BE9, BF9 & BG9

This is what I have tried:

=IF(AND(E9=3,ISNUMBER(Z9)),(F9*BE9,G9*BF9,H9*BG9,0)

I know that the formula is incorrect, please can anyone help?
Many thanks
Amanda

 

[Teethless mama]

=IF(AND(E9=3,ISNUMBER(Z9)),F9*BE9&", "&G9*BF9&", "&H9*BG9,0)

 

[Amanda D]

Thanks for the quick response.

I have just entered this in and the result shows in the cell as:

200,100,200

and so on.

I am getting the correct answers but need these adding together so this
answer would be 500

Can you help again?
Thanks

 

[Rick Rothstein \(MVP - VB\)]

Assuming your "and so on" means to keep multiplying, then adding, till the
end of the grid, try this formula...

=IF(AND(E9=3,ISNUMBER(Z9)),SUMPRODUCT(OFFSET(F9,0,0,1,ROWS(1:100))*OFFSET(BE9,0,0,1,ROWS(1:100))),0)

Rick

 

[Amanda D]

Thanks for your help, that works a treat!

Replace the occurrences of &", "& by +

 

[Rick Rothstein \(MVP - VB\)]

Perhaps we should reduce the 100 cell offset range to 50 cells lest the one
starting at F9 overlaps the one starting at BE...

=IF(AND(E9=3,ISNUMBER(Z9)),SUMPRODUCT(OFFSET(F9,0,0,1,ROWS(1:50))*OFFSET(BE9,0,0,1,ROWS(1:50))),0)

Rick