How to summarize data and deduct on second sheet? (Formula)
 

-- Hi again,

Let us say SHEET 1 is the input sheet that is based on date. SHEET 2
contains list of Accounts with corresponding credit limit. In SHEET 2 B1 is
account name, C1 is credit limit, D1 is Outstanding Balance, E1 is credit
limit net of outstanding balance.

How does D1 view all that is encoded in SHEET 1 Recognizes account name and
outstanding balance adding it all up before E1 can deduct from credit limit
to get credit limit for net balance credit limit.

joromajr

How to summarize data and deduct on second sheet? (Formula)
 

-- follow up--

SHEET 1: a1 is account name, b1 invoice number, c1 invoice amount.

SHEET 2: b1 Account name, c1 credit limit, d1 outstanding balance, e1 Net
Credit limit.

I need for column d to search account name and sum up corresponding invoice
amount.

joromajr


"joromajr" wrote:


-- Hi again,

Let us say SHEET 1 is the input sheet that is based on date. SHEET 2
contains list of Accounts with corresponding credit limit. In SHEET 2 B1 is
account name, C1 is credit limit, D1 is Outstanding Balance, E1 is credit
limit net of outstanding balance.

How does D1 view all that is encoded in SHEET 1 Recognizes account name and
outstanding balance adding it all up before E1 can deduct from credit limit
to get credit limit for net balance credit limit.

joromajr

How to summarize data and deduct on second sheet? (Formula)
 

Assuming the account names are consistant across both fields, You could use a
sumif.

=SUMIF(Sheet1!G:G,B2,Sheet1!H:H)

This is for the account number being in Column G and the value in Column H.
You can specific the entire column so that you don't have to worry about
extending the data range when there are more entries
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"joromajr" wrote:


-- Hi again,

Let us say SHEET 1 is the input sheet that is based on date. SHEET 2
contains list of Accounts with corresponding credit limit. In SHEET 2 B1 is
account name, C1 is credit limit, D1 is Outstanding Balance, E1 is credit
limit net of outstanding balance.

How does D1 view all that is encoded in SHEET 1 Recognizes account name and
outstanding balance adding it all up before E1 can deduct from credit limit
to get credit limit for net balance credit limit.

joromajr