IF AND problem

A

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
 
A

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
 
R

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top