Need Formula for Sum of Lowest 3 in list of 5 Random Cell Numbers

[bu08usc11]

I have a list of numbers in J8 - J21 and L8 - L21. I need to be able to select any 5 and have the formula sum the lowest three. For example, a sum formula I have is =SUM(L18,L14,L15,J12,J15). I need to be able to convert that formula so that it will sum the lowest three numbers in those corresponding cells. I know how to do sum of lowest/highest in a sequential cell like A1:A10 but not when they are random like that. Any thoughts?

[6StringJazzer]

Try this:

Please Login or Register  to view this content.

[bu08usc11]

Try this:

Please Login or Register  to view this content.

1) Should it be SMALL since I want lowest?

2) Where in that formula should I put the cell I want it to pull from? (i.e. L18,L14,L15,J12,J15)

[6StringJazzer]

You could use SMALL (I forgot about SMALL) and then the second arguments would be 1, 2, 3 respectively.

I think I misunderstood your explanation. SMALL requires an array, that is, a contiguous range. You have a discontiguous range. I can't think of a way to do that without a macro but maybe someone else will contribute.

[Tony Valko]

SMALL requires an array, that is, a contiguous range.

SMALL (and LARGE) will accept multiple area references:

=SMALL((A1:A5,C1:C5),1)

Maybe they want something like this:

Data Range

	A	B	C	D	E
1	67		35		90
2	57		90
3	76		55
4	11		44
5	84		49
6	------	------	------	------	------

This formula entered in e1:

=SUM(SMALL((A1:A5,C1:C5),{1,2,3}))

[6StringJazzer]

That is very slick Tony, I was not familiar with that syntax for multiple ranges, or that SMALL would produce an array result if you use an array as the second argument.

[kvsrinivasamurthy]

Try this ARRAY formula,

Please Login or Register  to view this content.

To enter array formula
Paste the formula in cell
Press F2
Press Ctrl+Shift+Enter keys together
{} is seen around the formula.

[Tony Valko]

Try this ARRAY formula,
=SUM(SMALL((L18,L14,L15,J12,J15),{1,2,3}))

That formula does not need to be array entered. Just a normal enter will do.

[bu08usc11]

Try this ARRAY formula,

Please Login or Register  to view this content.

To enter array formula
Paste the formula in cell
Press F2
Press Ctrl+Shift+Enter keys together
{} is seen around the formula.

PERFECT. Thanks!