How do I enter formula that adds a range if less than a number or

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rose M - 21 Dec 2005 19:40 GMT

I want to write formula that adds amounts in a range ofcells and if < a
number, enter sum, but if => than number, only add difference between sum and
number? I believe it to be an sumif function but can't seem to layout
formula.

Reply to this Message

Biff - 21 Dec 2005 19:48 GMT

Confusing explanation! Can you reword it and include an example?

Biff

>I want to write formula that adds amounts in a range ofcells and if < a
> number, enter sum, but if => than number, only add difference between sum
> and
> number? I believe it to be an sumif function but can't seem to layout
> formula.

Reply to this Message

Bob Phillips - 21 Dec 2005 20:30 GMT

=SUMPRODUCT(--(A1:A10>10),A1:A10-10)

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> I want to write formula that adds amounts in a range ofcells and if < a
> number, enter sum, but if => than number, only add difference between sum and
> number? I believe it to be an sumif function but can't seem to layout
> formula.

Reply to this Message