ExcelBanter - =sum formula using arrays

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - =sum formula using arrays (https://www.excelbanter.com/excel-programming/303942-%3Dsum-formula-using-arrays.html)

John[_64_]

=sum formula using arrays

Column A contains numbers, such as:
6,000
9,000
8,000

I want to sum column A, but if a number is greater than 7,000, I only want
7,000 included in my sum. The result of the above amounts I am looking for
is 20,000. I think I need an array formula, but not sure what the formula
is. Thanks

Frank Kabel

=sum formula using arrays

Hi
try the following array formula
=SUM(IF(A1:A37000,7000,A1:A3))

--
Regards
Frank Kabel
Frankfurt, Germany

John wrote:
Column A contains numbers, such as:
6,000
9,000
8,000

I want to sum column A, but if a number is greater than 7,000, I only
want 7,000 included in my sum. The result of the above amounts I am
looking for is 20,000. I think I need an array formula, but not sure
what the formula is. Thanks

All times are GMT +1. The time now is 12:37 AM.