Multiple formulas based on value of a cell

[Meg83]

Hi,

I'm hoping to get some help on this - been struggling for the last two days to get it to work. I want to create a formula where if the value in cell D1 is blank or 0, it returns 0, but if the value is greater than 0 apply the following formula: SUMIF(D2,">=1",A2)+((D2-1)*B2)

Basically if the I want a 0 value if the quantity is 0, if the quantity is 1 the return value should be 10, if the quantity is 2 the value should be 15 and then increase by 5 for each addition to the quantity.

I almost got it to work with this formula - but it returned a value of FALSE when the quantity was 1:
=IF(AND(D2>=0,D2<=0),D2*0,IF(D2=1,D2<1,SUMIF(D2,">=1",A2)+((D2-1)*B2)))


Any advice would be greatly appreciated.
Thanks

 

[Fluff]

Hi & welcome to MrExcel.
Could D2 ever be less than 0, if so what should happen?

 

[snjpverma]

IF(D2,SUMIF(D2,">=1",A2)+((D2-1)*B2),0)

 

[Meg83]

No, D2 could be blank, 0, or <0

 

[Fluff]

So what should happen if it's less than 0?

 

[Meg83]

IF(D2,SUMIF(D2,">=1",A2)+((D2-1)*B2),0)

that worked - I knew there had to be a simple solution - I was over thinking it.

THANK YOU THANK YOU THANK YOU!!

 

[snjpverma]

that worked - I knew there had to be a simple solution - I was over thinking it.

THANK YOU THANK YOU THANK YOU!!

I am glad it works for you. However, kindly check if it's giving a desired output when D2 is negative, as pointed by Fluff.