Sumif formula need help

[Pete Henchey]

Hi

Could somebody offer some advice please? Im trying to create what I thought would be a simple formula but after messing about with If and Sumif, I cant make it work.

I'm trying to compare two cell values, if value (a) is greater than value (b) I'd like to subtract value (b) from (a).

Like wise if (b) is greater than (a) I'd like to add them together. Ive tried:

=IF(G7>F7,SUM(F7+G7),SUM(F7-G7)) amongst other formula efforts, but the formulas just add the two values and I cant get the subtraction working.

example: cell (F7)= 114000, and cell (G7)= 128000 - I can get the addition of both ok.

If I make (F7) a higher value than (G7) It wont subtract G7 from F7.

Hope this makes sense?

Any help appreciated

Pete

[#2]

Pete,

what's the purpose of this?

=MMULT((F7+G7*{1,-1}),COUNTIFS(G7,{">";"<="}&F7))

[#3]

Hello

Thanks for responding, Unfortunately the formula suggested doesn't seem to add the two cells together.

In response to your question, I'm trying to put a a weekly figure into a graph, but its important to see if the current total is higher or lower than the previous week. Its important to add the two cells if this weeks figures are greater than last week, but also to subtract this week from last week if the number has declined from the previous week.

Thanks

Pete

[#4]

My formula gives the same results as your formula.

In your formula you can omit the SUM() function.

=IF(G7>F7,F7+G7,F7-G7)

[#5]

Thanks but unfortunately my formula doesn't give the correct result hence the post on here, unfortunately the formula doesn't effectively subtract Cell G from Cell F when F has a higher value than G.

[#6]

Please check ISNUMBER(F7) and ISNUMBER(G7).

[#7]

I'm sorry I don't follow

[#8]

Type these formula in two cells and report the results back.

[#9]

You've cracked it! Thanks Detlef the data I was using from an external source had a comma in the value e.g. 114,820.
So now removing the comma excel recognises the cell value as a number.

Thanks for your help.

[#10]

Glad, I was on the right track.