counting the first six entries

alkarkar

Board Regular
Joined
Sep 18, 2005
Messages
125
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
 

Some videos you may like

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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Sep 18, 2005
Messages
125
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
Joined
Sep 18, 2005
Messages
125
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
Joined
May 28, 2005
Messages
47,475
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 18, 2005
Messages
125

ADVERTISEMENT

Thank you all for your answers.
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
Joined
May 28, 2005
Messages
47,475
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 18, 2005
Messages
125
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,645
Members
412,335
Latest member
cinciri99
Top