Sumif, Sumproduct or....?

  • Thread starter Thread starter Ricky
  • Start date Start date
R

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
 
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
 
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
 
Clever

--


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

Regards,

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

Ricky
 
Back
Top