lookup and sum
 

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

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,

--
Regards,

David Billigmeier



"Marc S" wrote:

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

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!
==============================================


"Marc S" wrote in message
...
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

try

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

where sheet2!b3:b9 is the range containing the account number on the
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 to
work.

R


--
Ruthki
------------------------------------------------------------------------
Ruthki's Profile: http://www.excelforum.com/member.php...o&userid=24503
View this thread: http://www.excelforum.com/showthread...hreadid=396284