# Sum of acount number in range

#### BASSET

##### New Member
I have a detailed report with acc numbers and balances. On a seperate sheet I have grouped the range of acc numbers in a column, for example 814000-814500. Is there a function to use to search for the range of account numbers (814000-814500) in the detailed report and then return the total amount (sum) for all the account numbers in the range specified.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### DonkeyOte

##### MrExcel MVP
lets say on sheet 1 Col A you have account numbers (rows 1 to 5000) and in Col B you have the amounts.

On your other sheet you have the account list - say starting in A1:

=SUMIF(Sheet1!\$A\$1:\$A\$5000,\$A1,SHEET1!\$B\$1:\$B5000)

You can then copy this down for all the other account numbers on your list.

#### BASSET

##### New Member
In cell A1, there is typed "814000-814500" in stead on all account numbers seperately. The sumif function does not appear to pick up the range of acc numbers specified?

#### eliW

##### Well-known Member
On 2002-10-24 03:29, BASSET wrote:
In cell A1, there is typed "814000-814500" in stead on all account numbers seperately. The sumif function does not appear to pick up the range of acc numbers specified?
Try this formula for the same example as below:

=SUMPRODUCT((A34:A500>=814000)*(A34:A500<=814500),L34:L500)

Eli

Was editted with the exact RANGES
This message was edited by eliW on 2002-10-24 04:32

##### MrExcel MVP
On 2002-10-24 03:29, BASSET wrote:
In cell A1, there is typed "814000-814500" in stead on all account numbers seperately. The sumif function does not appear to pick up the range of acc numbers specified?

Would you provide the following info...

1) What is the exact range of acc nums and blances you speak off as detailed report?

2) What is the name of the worksheet you want totals for groups of acc nums?

#### BASSET

##### New Member
On 2002-10-24 04:00, Aladin Akyurek wrote:
On 2002-10-24 03:29, BASSET wrote:
In cell A1, there is typed "814000-814500" in stead on all account numbers seperately. The sumif function does not appear to pick up the range of acc numbers specified?

Would you provide the following info...

1) What is the exact range of acc nums and blances you speak off as detailed report?

2) What is the name of the worksheet you want totals for groups of acc nums?

Range for account numbers: Cell A34 - A500
Range for balances: Cell L34 - L500
Name of worksheet - DETAILED IS FS

##### MrExcel MVP
On 2002-10-24 04:14, BASSET wrote:
Range for account numbers: Cell A34 - A500
Range for balances: Cell L34 - L500
Name of worksheet - DETAILED IS FS

Select \$A\$34:\$A\$500 in DETAILED IS FS, go to the Name Box on the Formula Bar, type ACCOUNTS, and hit enter.

Select \$L\$34:\$L\$500 in DETAILED IS FS, go to the Name Box on the Formula Bar, type BALANCES, and hit enter.

In the destination worksheet do the following:

In A1 enter: The lower limit acc number of the first grouping.
In A2 enter: The lower limit acc number of the second grouping.
In A3 enter: The lower limit acc number of the third grouping.

etc. As you see, this replaces the method you had in mind...

In B2 in the destination worksheet enter:

=SUMIF(ACCOUNTS,">="&A1,BALANCES)-SUMIF(ACCOUNTS,">="&A2,BALANCES)

Copy down this formula as far as needed.

Replies
1
Views
128
Replies
5
Views
289
Replies
4
Views
148
Replies
4
Views
325
Replies
2
Views
177

1,181,531
Messages
5,930,443
Members
436,738
Latest member
JFry

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