counting the first six entries

[alkarkar]

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

 

[barry houdini]

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]

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]

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]

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:

=IF(A1="","",SUMPRODUCT(--(D1:D$100=D1),A1:A$100,--(C1:C$100<=C1+5)))

Mr Excel 05 11 20.xls
	A	B	C	D
1	2	13	1	1
2			1	1
3			1	1
4	1	12	2	1
5	4	14	3	1
6			3	1
7	0	10	4	1
8			4	1
9			4	1
10	2	12	5	1
11			5	1
12	4	14	6	1
13			6	1
14			6	1
15	1	10	7	1
16			7	1
17			7	1
18			7	1
19	3	9	8	1
20	0	6	9	1
21	2	6	10	1
22			10	1
23	4	4	11	1
24			11	1
25			11	1
26	1	14	12	2
27			12	2
28	5	16	13	2
29			13	2
30	3	11	14	2
Count Six

 

[Zack Barresse]

Cross-posted: http://www.ozgrid.com/forum/showthread.php?t=42701

 

[alkarkar]

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]

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
	A	B	C	D
18
19	3	15
20	0	15
21	2	15
22
23	4	17
24
25
26	1	14
27
28	5	16
29
30	3	11
31	0	10
32	4	14
Count Six (2)

 

[alkarkar]

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.