Conditional sum

D

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.
 
J

Jason Morin

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

This sums everything in column A greater than 141.

HTH
Jason
Atlanta, GA
 
F

Frank Kabel

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

Earl Kiosterud

Deep,

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

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

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




.
Click to expand...
 
D

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


.
Click to expand...
 
Top