# 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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### barry houdini

##### MrExcel MVP
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

#### alkarkar

##### Board Regular
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

#### alkarkar

##### Board Regular
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?

#### Peter_SSs

##### MrExcel MVP, Moderator

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

#### alkarkar

##### Board Regular

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.

#### Peter_SSs

##### MrExcel MVP, Moderator
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)

#### alkarkar

##### Board Regular
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
7
Views
47
Replies
3
Views
331
Replies
1
Views
134
Replies
1
Views
169
Replies
4
Views
56