# counting over and summing down

#### dtlucius

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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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
ABCD
1"-----A ---------B"**Result:
21- A05**11088
3*2,772*0
4*2,772*0
5*2,772*0
6*2,772*0
Sheet1

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
ABCD
1"-----A ---------B"**Result:
21- A05**4
3*2,772*0
4*2,772*0
5*2,772*0
6*2,772*0
Sheet1

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:

Code:
``=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

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

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.

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

Code:
``````=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)))))``````

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))

Try:

Code:
``=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

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

Matty

Replies
10
Views
272
Replies
1
Views
178
Replies
12
Views
378
Replies
1
Views
104
Replies
9
Views
216

1,206,971
Messages
6,075,922
Members
446,170
Latest member
zzzz02

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