SUMPRODUCT (SMALL) FORMULA

[Digby]

I have a row of numerical values that contain a zero in some cells. I want to count the smallest eight values excluding the cells with zero values. A number of cells have the same value, e.g. there could, conceivably, be more than eight with the lowest value. Does this have an impact on the formula?

I have attempted to modify the formula to insert the element that will ignore any zero values without any success.

The formula will be entered into cell E6.

My current formula is =SUMPRODUCT(SMALL(D13:D64,{1,2,3,4,5,6.7.8}))/C3. How do I correct this formula to so that cells with a zero value are ignored?

Thanks in advance

D

 

[Eric W]

Welcome to the MrExcel forum!

Try:

=SUM(AGGREGATE(15,6,1/(1/D3:D64),{1,2,3,4,5,6,7,8}))/C3

 

[Digby]

=SUM(AGGREGATE(15,6,1/(1/D3:D64),{1,2,3,4,5,6,7,8}))/C3

Thanks Eric, unfortunately when I enter the formula suggested it returns #NAME? UNKNOWN FUNCTION 'AGGREGATE'

 

[Fluff]

Are you using 365, or an earlier version?
Also what language is your version of Excel.

 

[Dave Patton]

Eric's solution should work for you at least with Excel 2010 and later
I did not include the C3 calculation

You stated Row but your formula shows a Column of data
I used a range B1:B8 then used the =COUNTIF(D13:D64,0)
Each formula gives the correct result.

T202011a.xlsm
	A	B	C	D	E
1		29	28
2		30	35
3		31	35
4		32	35
5		33	35
6		34
7		35
8		36
9
10
11
12
13				1
14				2
15				3
16				4
17				5
18				6
19				7
20				7
21				7
22				10
23				11
24				12
25				13
26				14
27				15
28				16
29				17
30				18
31				19
32				20
33				21
34				22
35				23
36				24
37				0
38				0
39				0
40				0
41				0
42				0
43				0
44				0
45				0
46				0
47				0
48				0
49				0
50				0
51				0
52				0
53				0
54				0
55				0
56				0
57				0
58				0
59				0
60				0
61				0
62				0
63				0
64				0
9b

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(D13:D64,0)
C2	C2	=SUM(SMALL(D13:D64,{29,30,31,32,33,34,35,36}))
C3	C3	=SUM(SMALL(D13:D64,B1:B8))
C4	C4	=SUMPRODUCT(SMALL(D13:D64,B1:B8))
C5	C5	=SUM(AGGREGATE(15,6,1/(1/D3:D64),{1,2,3,4,5,6,7,8}))
B1:B8	B1	=ROW()+$C$1
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Digby]

Are you using 365, or an earlier version?
Also what language is your version of Excel.

Fluff, thanks for your help, I use MS365 - en - us version 16.013328.20356. In my call for help I used the term 'row' and meant 'column'. The column starts life unpopulated and starts at D13 through to D64. Each cell in the column is, over time, populated with a value, a whole number, e.g. 66. When, say, nine cells in descending order have been populated I want the sum of the smallest eight. However, some cells will have remained at zero and some cells may have the same value. As more cells are populated I want to obtain the sum of the smallest eight excluding zero. There are 52 cells in total. When all the cells are populated there will be a number of zero values and it is likely that the smallest value will have been entered on more than eight occasions, I still want the sum to be the value of the eight smallest.

I trust this helps, and again thanks.

D

 

[Digby]

Eric's solution should work for you at least with Excel 2010 and later
I did not include the C3 calculation

You stated Row but your formula shows a Column of data
I used a range B1:B8 then used the =COUNTIF(D13:D64,0)
Each formula gives the correct result.

T202011a.xlsm
	A	B	C	D	E
1		29	28
2		30	35
3		31	35
4		32	35
5		33	35
6		34
7		35
8		36
9
10
11
12
13				1
14				2
15				3
16				4
17				5
18				6
19				7
20				7
21				7
22				10
23				11
24				12
25				13
26				14
27				15
28				16
29				17
30				18
31				19
32				20
33				21
34				22
35				23
36				24
37				0
38				0
39				0
40				0
41				0
42				0
43				0
44				0
45				0
46				0
47				0
48				0
49				0
50				0
51				0
52				0
53				0
54				0
55				0
56				0
57				0
58				0
59				0
60				0
61				0
62				0
63				0
64				0
9b

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(D13:D64,0)
C2	C2	=SUM(SMALL(D13:D64,{29,30,31,32,33,34,35,36}))
C3	C3	=SUM(SMALL(D13:D64,B1:B8))
C4	C4	=SUMPRODUCT(SMALL(D13:D64,B1:B8))
C5	C5	=SUM(AGGREGATE(15,6,1/(1/D3:D64),{1,2,3,4,5,6,7,8}))
B1:B8	B1	=ROW()+$C$1
Press CTRL+SHIFT+ENTER to enter array formulas.

Eric's solution should work for you at least with Excel 2010 and later
I did not include the C3 calculation

You stated Row but your formula shows a Column of data
I used a range B1:B8 then used the =COUNTIF(D13:D64,0)
Each formula gives the correct result.

T202011a.xlsm
	A	B	C	D	E
1		29	28
2		30	35
3		31	35
4		32	35
5		33	35
6		34
7		35
8		36
9
10
11
12
13				1
14				2
15				3
16				4
17				5
18				6
19				7
20				7
21				7
22				10
23				11
24				12
25				13
26				14
27				15
28				16
29				17
30				18
31				19
32				20
33				21
34				22
35				23
36				24
37				0
38				0
39				0
40				0
41				0
42				0
43				0
44				0
45				0
46				0
47				0
48				0
49				0
50				0
51				0
52				0
53				0
54				0
55				0
56				0
57				0
58				0
59				0
60				0
61				0
62				0
63				0
64				0
9b

Cell Formulas
Range	Formula
C1	C1	=COUNTIF(D13:D64,0)
C2	C2	=SUM(SMALL(D13:D64,{29,30,31,32,33,34,35,36}))
C3	C3	=SUM(SMALL(D13:D64,B1:B8))
C4	C4	=SUMPRODUCT(SMALL(D13:D64,B1:B8))
C5	C5	=SUM(AGGREGATE(15,6,1/(1/D3:D64),{1,2,3,4,5,6,7,8}))
B1:B8	B1	=ROW()+$C$1
Press CTRL+SHIFT+ENTER to enter array formulas.

Dave, thanks for your prompt response, appreciated. My apology for the confusion, the sum required is a column. I will try your suggestions but in the meantime some, hopefu;lly, clarity;

Also what language is your version of Excel.

Fluff, thanks for your help, I use MS365 - en - us version 16.013328.20356. In my call for help I used the term 'row' and meant 'column'. The column starts life unpopulated and starts at D13 through to D64. Each cell in the column is, over time, populated with a value, a whole number, e.g. 66. When, say, nine cells in descending order have been populated I want the sum of the smallest eight. However, some cells will have remained at zero and some cells may have the same value. As more cells are populated I want to obtain the sum of the smallest eight excluding any zero. There are 52 cells in total. When all the cells are populated there will be a number of zero values and it is likely that the smallest value will have been entered on more than eight occasions, I still want the sum to be the value of the eight smallest.

So I need to establish the sum of the eight smallest from the moment the 9th cell in desending order as a number. Each cell corresponds to a weekly event. It is possible that when nine are populated that some will be at zero. If the formula only works when eight out of the fifty two have a number greater tha zero, then that's fine. FYI I have the a similar column that counts the largest, and this works with =SUMPRODUCT(LARGE(E13:E64,{1,2,3,4,5,6,7,8}))/C3. If I change 'LARGE' to 'SMALL' in this formula it returns the value of the eight smallest until there is a cell in the sequence with a zero.

 

[Digby]

Welcome to the MrExcel forum!

Try:

=SUM(AGGREGATE(15,6,1/(1/D3:D64),{1,2,3,4,5,6,7,8}))/C3

Eric, further to my previous reply I have found an ugly solution. Using my formula but with the cells that would have '0' being blank. It works but it's not nice.

Could you explain why your formula uses D3? In my worksheet the sum of the formula is linked to E4. D3 to D12 are used for other calculations.

Last whilst my get around works I would like to have a formula that works when '0' is ignored.

I use =SMALL(E13:E64,COUNTIF(E13:E64,0)+1) for another column where the sum is displayed in E10. It works fine. I have attempted to insert the part highlighted into my other formula in an endeavor to get the formula to ignore '0'. I've failed to find a solution.

 

[Dave Patton]

to Digby
1. It is not necessary to quote entire suggestions.
2. You stated "I use =SMALL(E13:E64,COUNTIF(E13:E64,0)+1)" Review or try the alternative/workaround that I suggested.
3. You can enter the suggestions where they are required.
4. The suggestions appear to answer your question.

​

 

[Eric W]

Could you explain why your formula uses D3?

That was a typo. It should be the range of your values:

=SUM(AGGREGATE(15,6,1/(1/D13:D64),{1,2,3,4,5,6,7,8}))/C3