# counting the first six entries

#### alkarkar

##### Board Regular
Hello averyone!
My 40 years of age don't help me ,so here is my quest:
One column with blanks, zeros, one digit numbers,not specific order. Eg:
A B
1 2
2
3
4 1
5 4
6
7 0
8
9
10 2
11
12 4
13
14
15 1
16
17
18
19 3
20 0
etc.
I want at B1 to sum the real six entries from A1 And below(not counting blanks, in above 2+1+4+0+2+4=13).,then copy paste it down.At B2 and B3 will be blank(could be done with 'IF(A2="";"";....my querry here...) but at B4 will be the sum from A4 and below (six real enties, in the example above cells A4,A5,A7,A10,A12,A15 >>1+4++0+2+4+1=12)
Many thanks, Alex

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Alex,

Assuming your values go as far down column A as A100 try this formula in B1

=IF(A1<>"",SUM(A1:INDEX(A1:A\$100,SMALL(IF(A1:A\$100<>"",ROW(A1:A\$100)-ROW(A\$1)+1),MIN(6,COUNT(A1:A\$100))))),"")

confirmed with CTRL+SHIFT+ENTER and copied down column

Thanks Houdini.
I don't know how you did it but it's in your name, 'Houdini' .
The problem is how to change the 'borders' of a range at your will. In my problem i wanted the last cell of the range (A?) to be changable. From my small jurney in Excel i found the same-similar problem many times.
Thanks again Houdini

Sorry Houdini, the formula is working if you apply it at the WHOLE range with data (A1:A100). I wanted to apply the formula to three segments of the range (eg. A1:A25, A26:A54, A55:A100). #REF!
Any ideas? I guess i must change it little ,but how?

alkarkar

Could we use a couple of 'helper' columns like C and D below? These columns could be hidden if required. Hope I have understood correctly about the breaks at row 25 and 54.

Column C is showing where each 'real' number starts. The formula in C1 is =0+ISNUMBER(A1) and the formula in C2 (copied down) is =C1+ISNUMBER(A2)

Column D records which of your three sections (1-25, 26-54, 55-100) the row is in. The formula in D1 (copied down) is =LOOKUP(ROW(),{0,26,55},{1,2,3})

The formula in B1 (copied down is:
Code:
``=IF(A1="","",SUMPRODUCT(--(D1:D\$100=D1),A1:A\$100,--(C1:C\$100<=C1+5)))``
Mr Excel 05 11 20.xls
ABCD
121311
2 11
3 11
411221
541431
6 31
701041
8 41
9 41
1021251
11 51
1241461
13 61
14 61
1511071
16 71
17 71
18 71
193981
200691
2126101
22 101
2344111
24 111
25 111
26114122
27 122
28516132
29 132
30311142
Count Six

I only checked Houdini's answer and is working now with only one small change (took me one day to find it).
Houdini gave this formula:
=IF(A1<>"",SUM(A1:INDEX(A1:A\$100,SMALL(IF(A1:A\$100<>"",ROW(A1:A\$100)-ROW(A\$1)+1),MIN(6,COUNT(A1:A\$100))))),"")
And the working one is:
=IF(A1<>"",SUM(A1:INDEX(A\$1:A\$100,SMALL(IF(A1:A\$100<>"",ROW(A1:A\$100)-ROW(A\$1)+1),MIN(6,COUNT(A1:A\$100))))),"")
The difference is the \$ aftrer INDEX(A >>>INDEX(A\$1
And is working with the 'segments-breaks' wich i want to separate my data.
Thanks a lot all of you.

alkarkar said:
And the working one is:
=IF(A1<>"",SUM(A1:INDEX(A\$1:A\$100,SMALL(IF(A1:A\$100<>"",ROW(A1:A\$100)-ROW(A\$1)+1),MIN(6,COUNT(A1:A\$100))))),"")

And is working with the 'segments-breaks' wich i want to separate my data.
I can't see anything in this formula that changes anything at rows 26 or 55? And when I tested it it, nothing seemed to change at or around the row 25 or 26 mark.

Here is a small sample using the formula above. With this data, didn't you want cell B19 to only add 3+0+2+4 (that is not adding past row 25)?
Mr Excel 05 11 20.xls
ABCD
18
19315
20015
21215
22
23417
24
25
26114
27
28516
29
30311
31010
32414
Count Six (2)

As i see it , i wasn't clear enough. What i can now do is:
My data >> three sections A1:A300, A301:A600, A601:A1000
1. First step copy-paste formula to A1, change manualy the lower border of the range in formula, ie> A100 >A300 , copy down to A300.
2.copy-paste formula to A301, change manualy the upper and lower border of the range in formula, ie> A1>A301 ,A100 >A600 , copy down to A600
3. same as step 2 (different borders).
I get what i want, MANUALLY changing some entries.
As i'm now looking your solution, it looks more powerfull. I defently check it later .
Thanks Peter.

Replies
5
Views
201
Replies
3
Views
71
Replies
4
Views
236
Replies
1
Views
183
Replies
3
Views
101

1,212,057
Messages
6,105,652
Members
447,974
Latest member
misspancake

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back