Hi
I have a range of data in Cloumn B2:B6
48
59
40
42
32


Now in the Column B7 i want to compare the data in each every cell if that
data is greater than 40 then it must substract from 40 and display in B7
For example : B2(i.e. 48) 40 so it must substract 48-40 =8
B3(i.e 59)40 so it must substract 59-40 = 19
B4 (i.e. 40) = 40 so there must not be any action on
this cell value

so the value in B7 must be 8+19+2=29

Can anyone suggest me how to use this formula.

Try this

=SUM(IF(B2:B640,B2:B6-40,""))

This is an array formula and must be entered with CTRL+Shift+Enter not just
enter. If you do it correctly Excel will put curly brackets around it {}. You
can't type these yourself.

Mike

"Raghu" wrote:

Hi
I have a range of data in Cloumn B2:B6
48
59
40
42
32


Now in the Column B7 i want to compare the data in each every cell if that
data is greater than 40 then it must substract from 40 and display in B7
For example : B2(i.e. 48) 40 so it must substract 48-40 =8
B3(i.e 59)40 so it must substract 59-40 = 19
B4 (i.e. 40) = 40 so there must not be any action on
this cell value

so the value in B7 must be 8+19+2=29

Can anyone suggest me how to use this formula.

Hi,

Another approach:
=SUMPRODUCT((A1:A5-40)*(A1:A5=40))

--
Thanks,
Shane Devenshire


"Raghu" wrote:

Hi
I have a range of data in Cloumn B2:B6
48
59
40
42
32


Now in the Column B7 i want to compare the data in each every cell if that
data is greater than 40 then it must substract from 40 and display in B7
For example : B2(i.e. 48) 40 so it must substract 48-40 =8
B3(i.e 59)40 so it must substract 59-40 = 19
B4 (i.e. 40) = 40 so there must not be any action on
this cell value

so the value in B7 must be 8+19+2=29

Can anyone suggest me how to use this formula.