Select low numbers from range +

[glaskow4]

Select the 3 lowest numbers out of a range, disregard any row with less than 3 and add the resulting 3 lowest number together.

 

[Claus Busch]

Hi,

Am Sun, 31 Aug 2014 08:26:07 -0700 (PDT) schrieb (e-mail address removed):

Select the 3 lowest numbers out of a range, disregard any row with less than 3 and add the resulting 3 lowest number together.

try:
=SUMIFS(A1:A1000,A1:A1000,">3",A1:A1000,"<="&SMALL(A1:A1000,COUNTIF(A1:A1000,"<=3")+3))
or
=SUMPRODUCT(--(A1:A1000>3),--(A1:A1000<=SMALL(A1:A1000,COUNTIF(A1:A1000,"<=3")+3)),A1:A1000)


Regards
Claus B.

 

[Claus Busch]

Hi,

Am Sun, 31 Aug 2014 17:38:42 +0200 schrieb Claus Busch:

=SUMIFS(A1:A1000,A1:A1000,">3",A1:A1000,"<="&SMALL(A1:A1000,COUNTIF(A1:A1000,"<=3")+3))
or
=SUMPRODUCT(--(A1:A1000>3),--(A1:A1000<=SMALL(A1:A1000,COUNTIF(A1:A1000,"<=3")+3)),A1:A1000)

or
=SUMIF(A1:A1000,"<="&SMALL(A1:A1000,COUNTIF(A1:A1000,"<=3")+3))-SUMIF(A1:A1000,"<=3")


Regards
Claus B.

 

[glaskow4]

Hi,



Am Sun, 31 Aug 2014 08:26:07 -0700 (PDT) schrieb (e-mail address removed):






try:

=SUMIFS(A1:A1000,A1:A1000,">3",A1:A1000,"<="&SMALL(A1:A1000,COUNTIF(A1:A1000,"<=3")+3))

or

=SUMPRODUCT(--(A1:A1000>3),--(A1:A1000<=SMALL(A1:A1000,COUNTIF(A1:A1000,"<=3")+3)),A1:A1000)





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thank you! I'll give it a try.

 

[glaskow4]

Select the 3 lowest numbers out of a range, disregard any row with less than 3 and add the resulting 3 lowest number together.

Made a mistake was the three highest numbers but I was able to change Claus' formula.
Also suggested offline was:
=SUM((LARGE((D3:H3),3)),(LARGE((D3:H3),2)),(LARGE((D3:H3),1)))

 

[Claus Busch]

Hi,

Am Mon, 1 Sep 2014 11:24:42 -0700 (PDT) schrieb (e-mail address removed):

=SUM((LARGE((D3:H3),3)),(LARGE((D3:H3),2)),(LARGE((D3:H3),1)))

try:
=SUMIF(D3:H3,">="&LARGE(D3:H3,3))


Regards
Claus B.

 

[glaskow4]

Hi,



Am Mon, 1 Sep 2014 11:24:42 -0700 (PDT) schrieb (e-mail address removed):








try:

=SUMIF(D3:H3,">="&LARGE(D3:H3,3))





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Works great! Thanks!