Sum of acount number in range

[BASSET]

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.

 

[DonkeyOte]

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]

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]

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]

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]

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]

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.