counting over and summing down

[dtlucius]

I am looking to see if I can look up a cell and count over one row and down one, then sum a number of rows and stop summing when it runs into a cell that has nothing in it. I need it to find the item code A05 in Column A count over one, to column B and down one row to row 2 and sum all of the numbers down. Stopping in row 5 because there is nothing in row 6.
-----A ---------B
1- A05
2------------ 2,772
3------------ 2,772
4------------ 2,772
5------------ 2,772
6------------

 

[NiceGuyRitchie]

I'm not sure if this is exactly what you're looking to do but give it a shot. The SUM range is set for sum five rows.

Excel Workbook
	A	B	C	D
1	"-----A ---------B"	*	*	Result:
2	1- A05	*	*	11088
3	*	2,772	*	0
4	*	2,772	*	0
5	*	2,772	*	0
6	*	2,772	*	0
Sheet1

 

[NiceGuyRitchie]

Sorry, I misread your original post. For counting the number of rows, I believe this is more along the lines of what you are trying to accomplish...

Excel Workbook
	A	B	C	D
1	"-----A ---------B"	*	*	Result:
2	1- A05	*	*	4
3	*	2,772	*	0
4	*	2,772	*	0
5	*	2,772	*	0
6	*	2,772	*	0
Sheet1

 

[Matty]

If I understand correctly, perhaps:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:20px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>A05</td><td>
</td><td>
</td><td>A06</td><td style="text-align:right; ">10050</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>
</td><td style="text-align:right; ">2772</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td style="text-align:right; ">2772</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>
</td><td style="text-align:right; ">2772</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>
</td><td style="text-align:right; ">2772</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>A06</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>
</td><td style="text-align:right; ">10000</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>
</td><td style="text-align:right; ">50</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>A07</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td>
</td><td style="text-align:right; ">500</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td>
</td><td style="text-align:right; ">400</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
Array formula in E1 is:

=SUM(INDEX(B1:B20,MATCH(D1,A1:A20,0)+1):INDEX(B1:B20,MATCH(D1,A1:A20,0)+1+MATCH(TRUE,INDEX(B1:B20,MATCH(D1,A1:A20,0)+1):B100="",0)-1))

Which needs committing with CTRL+SHIFT+ENTER for it to work.

Matty

 

[dtlucius]

You had it right the first time, but I am more looking for a vlookup formula. Where it references the items code A05 in column A and sums the values down in column B starting in row 2 stopping in row 5 because there is nothing in row 6. I have a large number of items that I need this to reference. Some will only sum one row, and some will total as many at 15 rows. I will be doing this all on another tab in the workbook where I can just paste the report into the report tab in the workbook

 

[NiceGuyRitchie]

I can tell you that a VLookup will not sum a range. I believe Matty is on the right track with the nested Index/Match formula.

 

[Haseeb Avarakkan]

If col_A contains only text entries, try this with just ENTER;

=SUM(INDEX(B:B,MATCH(D1,A:A,0)):INDEX(B:B,MATCH(D1,A:A,0)+
LOOKUP(2^20,CHOOSE({1,2},65536,MATCH("*",INDEX(A:A,MATCH(D1,A:A,0)+1):A65536,0)))))

 

[dtlucius]

Matty,

You had it right. I got it to work perfect.
Is there anything I need to do different to get it to work referencing the item code on a different tab? PA is the report, and PA Worksheet is where i am trying to have the information displayed. this is the formula i have come up with do you see anything that i have done wrong?


<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2926269 class=xl63 height=17 width=64></TD></TR></TBODY></TABLE>
=sum(INDEX(PA!B:B,'PA Worksheet'!,MATCH('PA Worksheet'!A7,PA!A:A,0)+1):INDEX(PA!B:B,'PA Worksheet'!,MATCH('PA Worksheet'!A7,PA!A:A,0)+1+MATCH(TRUE,INDEX(PA!B:B,'PA Worksheet'!,MATCH('PA Worksheet'!A7,PA!A:A,0)+1):B100="",0)-1))

 

[Matty]

Try:

=SUM(INDEX(PA!B$[COLOR=Blue]1[/COLOR]:B$[COLOR=Red]20[/COLOR],MATCH(A7,PA!A$[COLOR=Blue]1[/COLOR]:A$[COLOR=Red]20[/COLOR],0)+1):INDEX(PA!B$[COLOR=Blue]1[/COLOR]:B$[COLOR=Red]20[/COLOR],MATCH(A7,PA!A$[COLOR=Blue]1[/COLOR]:A$[COLOR=Red]20[/COLOR],0)+1+MATCH(TRUE,INDEX(PA!B$[COLOR=Blue]1[/COLOR]:B$[COLOR=Red]20[/COLOR],MATCH(A7,PA!A$[COLOR=Blue]1[/COLOR]:A$[COLOR=Red]20[/COLOR],0)+1):PA!B$[COLOR=Red]20[/COLOR]="",0)-1))

Change the blue number to reflect the first row where your data sits and the red number to reflect a number somewhere past where the last row is ever likely to be.

With this set up you could have, say, A06 in cell A7 and A07 in cell A8 on your PA Worksheet, and when the formula is copied down it will update according to the new item code.

Hope this helps.

Matty

 

[Matty]

As per your PM, could you upload the file to Box or SkyDrive and I can pick it up from there?

Matty