G
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.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
C
Claus Busch
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.
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.
C
Claus Busch
Hi,
Am Sun, 31 Aug 2014 17:38:42 +0200 schrieb Claus Busch:
or
=SUMIF(A1:A1000,"<="&SMALL(A1:A1000,COUNTIF(A1:A1000,"<=3")+3))-SUMIF(A1:A1000,"<=3")
Regards
Claus B.
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.
G
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.
G
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)))
C
Claus Busch
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.
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.
G
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!