Formula

[Chuck Pringle]

I need help with a formula that will provide the sum of
the lowest for values in a specific range. Where there
are multiple values that are the same, I need only the
sum of four of them.

Any Help????

 

[Vasant Nanavati]

=SUM(SMALL(TheRange,ROW(1:4)))

entered as an array formula with <Ctrl> <Shift> <Enter>.

 

[Frank Kabel]

Hi
try
=SUM(SMALL(range,{1,2,3,4}))

 

[Frank Kabel]

Hi Vasant
in this case no need for an array formula

 

[Chuck]

Thank You

-----Original Message-----
Hi
try
=SUM(SMALL(range,{1,2,3,4}))

--
Regards
Frank Kabel
Frankfurt, Germany



.

 

[Vasant Nanavati]

True, Frank. Good catch.

Regards,

Vasant.

Hi Vasant
in this case no need for an array formula

 

[Harlan Grove]

in this case no need for an array formula

...

Did you try

=SUM(SMALL(TheRange,{1;2;3;4}))

and assume it would work exactly the same as

=SUM(SMALL(TheRange,ROW(1:4)))

?

Or did you implicitly change SUM to SUMPRODUCT? Vasant's formula, as written,
*DOES* need to be array-entered, or it'll just return MIN(TheRange). If SUM were
changed to SUMPRODUCT, then it wouldn't need to be array-entered.
...

 

[Vasant Nanavati]

Hi Harlan:

Actually I did test mine before posting and confirmed that it didn't work as
a regular formula. But I thought Frank's solution was better in this case
(only 4 elements) since it didn't need to be array-entered.

I didn't think about SUMPRODUCT.

Regards,

Vasant.

 

[Frank Kabel]

...
..

Did you try

=SUM(SMALL(TheRange,{1;2;3;4}))

and assume it would work exactly the same as

=SUM(SMALL(TheRange,ROW(1:4)))

Hi Harlan
yes this was my assumption (should have stated this though). Hope you
won't prove me wrong on this

Or did you implicitly change SUM to SUMPRODUCT? Vasant's formula, as
written, *DOES* need to be array-entered, or it'll just return
MIN(TheRange). If SUM were changed to SUMPRODUCT, then it wouldn't
need to be array-entered.

Ack