Formula Help??

[Jav_uk8]

Hi All,

Hi All,

I need a formula to calculate the following in column D (example of returned info in there):

Only bring across the cost (Col C) info from Red (Col A) that is more than £5.00, all others show 0 inc Red, Blue and Green.

I need it for a summary at the bottom (if possible) or could assign to each column D, which I could SUM at the bottom.

Hope this makes sense?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"][/TD]
[TD="align: center"]£2.29[/TD]
[TD="align: center"]£2.29[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"][/TD]
[TD="align: center"]£5.14[/TD]
[TD="align: center"]£0[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"][/TD]
[TD="align: center"]£1.01[/TD]
[TD="align: center"]£0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"][/TD]
[TD="align: center"]£5.85[/TD]
[TD="align: center"]£5.85[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Green[/TD]
[TD="align: center"][/TD]
[TD="align: center"]£10.47[/TD]
[TD="align: center"]£0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"][/TD]
[TD="align: center"]£9.05[/TD]
[TD="align: center"]£9.05[/TD]
[/TR]
</tbody>[/TABLE]

Cheers

 

[Special-K99]

in D1
=IF(C1>5,C1,0)
and copy down column D

 

[jmacleary]

Sorry special, I think youve misinterpreted the request. The formula needed I think is =IF(AND(A1="Red",C1>5),C1,0) - but that would only be right if the value in D1 was supposed to be 0.

 

[Jav_uk8]

Hi Special-K99,

Your formula returns the correct value but I only need the formula to return >£5.00 for Reds only.

 

[Jav_uk8]

Hi All,

Thanks for your replies, it has now come to light that I need to show the value from a sum that is >£5.00

Example - Sum of £5.05 (Col C) MINUS £5.00 (Col E), which returns the value of £0.05. Again only applying to Reds and the rest showing "0"

 

[Special-K99]

There is no "Column E"

 

[jmacleary]

Try this: =IF(AND(A1="Red",(C1-E1)>5),C1-E1,0)

 

[Jav_uk8]

It works!!!!

Thank you so much - Have a nice weekend all