Sum If - I think

[CandiC]

I have a worksheet with data in Columns D, E, F, G, and H that I will use to
calculate column I. If E2 contains a numeric value, then transfer that value
to I2, but if E2 is Blank or NA then use the product of (D2*G2), if colums
D2, E2, G2 are blank or NA then return the value of zero.

Can someone please help?

 

[Pete_UK]

You don't seem to be using the values from column F and H in your
description.

Try this formula in I2:

=IF(OR(D2="",D2="NA",G2="",G2="NA"),0,IF(OR(E2="",E2="NA"),D2*G2,IF
(ISNUMBER(E2),E2,"not defined")))

You can see that you have not been very precise in your description -
what happens if you have other text values in those cells?

Hope this helps.

Pete

 

[Francis]

try this
=IF(ISERROR(OR(E2="",D2="",G2="")),0,IF(E2>0,E2,D2*G2))

Does this do what you want?
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[CandiC]

Ok. I C. There will only be numerical values in any of the cells with the
exception of a NA# value, because the lookup didn't produce any results in
that colmn. I will try this and C if it works.

thank you,

Candi

 

[CandiC]

….D2...............E2……...............G2…………….............…..H2...................……I2…….
…Item QTY...Ext actual Cost... Unit Cost……Imported Unit Cost………….Actual Cost
.....2………..........1.00…........…..50…..................89……………………...1.00(=E2)
...10…………….<blank> or 0…..….25……………………50……….…………..250(D2*G2)
…5............…<blank> or 0……<blank>or 0…………10………………………50(D2*H2)
…20…………..<blank> or 0………<blank>or 0……<blank>or 0………………….(0)

Here is a summary of what I am looking at. D2= The item QTY, E2= the Actual
extended cost of the item, G2 =Unit Costs, and H2= imported unit costs. In
the event that I don't have an actual cost in columns E2 or G2 then I need to
multiply H2 by the item qty in D2 to give me an extended cost. However, I
would prefer to have the quantity specified in column E2 if there is one.

Thank you for your help.

 

[Anubis]

I have a worksheet with data in Columns D, E, F, G, and H that I will
use to
calculate column I. If E2 contains a numeric value, then transfer that
value
to I2, but if E2 is Blank or NA then use the product of (D2*G2), if
colums
D2, E2, G2 are blank or NA then return the value of zero.

Can someone please help?

Here you go:
=IF(ROUNDUP(E2^2,0)>0=TRUE,E2,IF(OR(ISBLANK(E2)=TRUE,ISNA(E2))=TRUE,D2*G2,IF(OR(AND(ISBLANK(D2)=TRUE,ISBLANK(E2)=TRUE,ISBLANK(G2)=TRUE),AND(ISNA(D2)=TRUE,ISNA(E2)=TRUE,ISNA(G2)=TRUE))=TRUE,0,0)))