Conditional sum

[Deepwater]

I am looking for a formula that will add cells based on
condition. Ex. If i have ten cells with different values
(130, 131, 143, 142, 144,130, 132,141,130.50,141.70)
The condition is, if any cell is greater than 141, it
should take only 141.

Any help will be appreciated.

Thanks.

 

[Jason Morin]

=SUMIF(A:A,">141")

This sums everything in column A greater than 141.

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SIFT+ENTER):
=SUM(MIN(A1:A100,141))

 

[Earl Kiosterud]

Deep,

=SUM(IF(A1:A10<141,A1:A10,141))

Entered as array formula (Ctrl-Shift-Enter).

 

[Deepwater]

Thanks Frank

IT is giving only the minimum number in the cells.

 

[Frank Kabel]

Hi
try the array formula
=SUM(IF(A1:A100>141,141,A1:A100))

 

[Guest]

Thanks Earl

It works..

-----Original Message-----
Deep,

=SUM(IF(A1:A10<141,A1:A10,141))

Entered as array formula (Ctrl-Shift-Enter).
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------




.

 

[Deepwater]

Thanks Frank for help.. it works

-----Original Message-----
Hi
try the array formula
=SUM(IF(A1:A100>141,141,A1:A100))

--
Regards
Frank Kabel
Frankfurt, Germany


.