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

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


"Amanda D" wrote:


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

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


"Teethless mama" wrote:

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


"Amanda D" wrote:


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

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" <Amanda D @discussions.microsoft.com wrote in message
...

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

Replace the occurrences of &", "& by +
--
David Biddulph

"Amanda D" wrote in message
...

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


"Teethless mama" wrote:

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


"Amanda D" wrote:


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

Thanks for your help, that works a treat!

"David Biddulph" wrote:

Replace the occurrences of &", "& by +
--
David Biddulph

"Amanda D" wrote in message
...

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


"Teethless mama" wrote:

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


"Amanda D" wrote:


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

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" <Amanda D @discussions.microsoft.com wrote in message
...

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