Adding the sum of cells that are the difference over a specific number

[chixdiggit]

So basically I have cells with numbers in them. What I want to do is add the amount over a specific amount... let's say 6
So if A1 had 7 entered and A2 had 9.5 entered the total would be 4.5.
Hope that makes sense.

[JohnTopley]

one way

=SUMPRODUCT((A1:A2-6))

[MrShorty]

Warning to mathophobes: this post contains algebra.

I would start with some algebra. You should be able to show that sum(ai-k)=sum(ai)-n*k where n is the number of entries. So, something like =sum(A1:A2)-count(A1:A2)*6

[chixdiggit]

Just tried both solutions and both worked! Thanks so much.
I was actually on the right path on my own based on the first reply. Thanks JT!
Mrshorty now has me rethinking some of my more complex formulas. Thanks Mr Shorty!

[chixdiggit]

One Problem I found with both formulas however. If the number is below 6 it will subtract the difference. I only want to add the amount over 6.
So if A1 had a 9 and A2 had a 5 the total would be 3.

[MrShorty]

From mine, it seems like it should be as simple as replacing the sum() and count() functions with appropriate sumif() and countif() or sumifs() and countifs().

=sumif(a1:a2,">6")-countif(a1:a2,">6")*6

[chixdiggit]

Thanks again Mr S.
It works though I'm not really understanding why haha. The *6 is really throwing me off.
If I remove the *6 I get a different answer obviously but I understand where it's coming from. Adding the *6 makes it the way I want but I don't understand why.
If you don't want to explain it I understand! Thanks again!

[JohnTopley]

Fyi

=sumproduct((a1:a3>6)*(a1:a3-6))

[MrShorty]

Did you attempt the algebra problem I described in post 3? Expanding the algebra:

y=(a1-6)+(a2-6)
y=a1+a2-6-6
y=(a1+a2)-2*6

Does that help?

[chixdiggit]

Thanks JT Appreciated.
And Yes MrShorty that does help. Thanks for taking the time to dumb it down for me.