lookup and sum

M

Marc S

I have a worksheet on which I need a sum of data for accounts using info from
another worksheet if the data on the second worksheet is associated with a
given account on the first worksheet.

Example:

On worksheet 1:

Acct Description Total
5001 Account 1 ???
5002 Account 2 ???
5003 Account 3 ???
5004 Account 4 ???
etc

I want to fill in the "Total" column for each account using info from
Worksheet 2:

Invoice Acct Total
1001 5001 100
5002 200
1002 5001 50
1003 5003 500
5002 300
1004 5004 300
5001 300

So for acct 5001, the total is 450
5002 - 500
5003 - 500
5004 - 300

I need to look on worksheet 2, match the account number from worksheet 1 and
sum the info from worksheet 2 in the total column on workheet 1.

Thanks in advance for help.

Marc
 
D

David Billigmeier

Marc -
Assume your account column in worksheet 1 starts in A1, and that "worksheet
2" is literally the name of the tab of your 'worksheet 2', try this formula:

=SUMPRODUCT(--('worksheet 2'!<range of your account number on worksheet
2>=A1),--('worksheet 2'!<range of your total column on worksheet 2))

I left the values I didn't know in within the <>, It's a little confusing
because I didn't have exact ranges, reply back with these and I will give you
an exact formula,
 
R

RagDyer

Assuming all data lists are in Columns A:C on their respective sheets, with
header rows, and data to Row25.

Try this:

=SUMIF(Sheet2!$B$2:$B$25,A2,Sheet2!$C$2:$C$25)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
R

Ruthki

try

=SUMIF(Sheet2!B3:B9,Sheet1!A2,Sheet2!C3:C9)

where sheet2!b3:b9 is the range containing the account number on th
sheet you are summing.
Where sheet!1a2 is the account number you want to sum for
Where C3:c9 is the range with the values in.

nb the two ranges must be exactly the same size for the formual t
work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top