Sumif, Sumproduct or....?

[Ricky]

Hello Experts,
How do you put together a formula that extract the numbers greater than 40,
then subtract each of the number in excess of 40 from the constant 40?
ie.
A1 45
A2 20
A3 55
A4 09
A5 62

The calculation should be (45-40 + 55-40 + 62-40) = 42.

I've tried =sumif(A1:A4>40,{but not unsure here because I can't subtract
each number from 40}). Perhaps, Sumif may not even be the right choice...?

Thanks in advance,
Ricky

 

[Peo Sjoblom]

One way,
do insert name, in the refers to box put

={40;40;40;40;40}

name it MyNmb

now you can use

=SUMPRODUCT(((A1:A5>40)*(A1:A5)-(MyNbm)*(A1:A5>40)))

or use it directly


=SUMPRODUCT(((A1:A5>40)*(A1:A5)-({40;40;40;40;40})*(A1:A5>40)))

or wuthout creating an array of 40s

=SUMPRODUCT(((A1:A5>40)*(A1:A5)-(SUBSTITUTE(A1:A5,A1:A5,40))*(A1:A5>40)))


--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Peo Sjoblom]

wuthout!? What happened to my spell checker?
Obviously the substitute part also creates an array of 40s, I meant
not hard coding them

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[CLR]

Maybe...........

=SUMIF(A:A,">40")-(COUNTIF(A:A,">40")*40)

Vaya con Dios,
Chuck, CABGx3

 

[Peo Sjoblom]

Clever

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[CLR]

Thank you Peo, your compliment is appreciated.

Vaya con Dios,
Chuck, CABGx3

 

[Ricky Pang]

Thank-you so much Peo and CLR. You're both very clever! I appreciate your
quick response.

Ricky