Subtract From two Columns and Total the difference

[Azad]

Hi,

I have two columns with values.i.e I would like to subtract A from B with a condition.

See sample formula I am using below

H K

10 15

5 3

28 30


={IF(((K16:K4398)-(H16:H4398))>0,SUM(K16-H4398),"X"}

However I am only getting X.I know the answer should be a numeric value.

Any help would be appreciated

 

[Hui]

Azad

If it is a normal formula then the format should be

=IF( Sum(K16:K4398)- Sum(H16:H4398) >0, SUM(K16-H4398), "X")


But the last Sum "SUM(K16-H4398)" is a strange format ?

Maybe it should be

SUM(K16:K4398) or SUM(H16:H4398) or SUM(K16:H4398)


The only time you see {} brackets is in Array Formula

and the user doesn't put those in Excel will do it for you

 

[Azad]

Hi,

Tried all the variations.Keep getting X

 

[TessaES]

If I understand correctly you want to sum all the differences when the value in column K is bigger than in H?


The easiest way is probably to use a helper column and first calculate the differences in column L with max(K2-H2,0) and then sum that.

 

[Hui]

Azad

This will do what TessaES suggested without a helper column

=+SUMPRODUCT(1*((H16:H4398)-(K16:K4398)>0),((H16:H4398)-(K16:K4398)))