Sum of acount number in range

BASSET

New Member
Joined
Oct 22, 2002
Messages
7
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Oct 22, 2002
Messages
7
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
Joined
Mar 2, 2002
Messages
1,934
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Oct 22, 2002
Messages
7
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,765
Messages
5,726,175
Members
422,660
Latest member
mrsteele

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top