Combining two worksheets into one
 

I have to wkshts with names and dollars paid, one for Jan to Jun, the second
for Jul to Dec. I need to find the names on both worksheets and add the
dollar amounts together. There is a formula that will help me put the names
together, I just can't remember, and am having no luck using the help button.
Can anyone give me a jump start?

Combining two worksheets into one
 

Your message was a little scare on info. What you are doing is
consolidating. A Google with search term <Excel consolidate will lead you
to many examples.
Here is one: http://support.microsoft.com/kb/214270
Do come back if more is needed
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Jamie" <Jamie @discussions.microsoft.com wrote in message
...
I have to wkshts with names and dollars paid, one for Jan to Jun, the
second
for Jul to Dec. I need to find the names on both worksheets and add the
dollar amounts together. There is a formula that will help me put the
names
together, I just can't remember, and am having no luck using the help
button.
Can anyone give me a jump start?

Combining two worksheets into one
 

I think that you're probably trying to remember SUMIF().

SUMIF() takes 3 parameters: (range to look through, what to match, range to
sum)
Lets say you have a bunch of names in column A and the amounts they paid in
column B. Your lists go from row 2 down to row 581.

In column C (or any unused column) at row 2 you could put a formula:
=SUMIF(A$2:A$581,A2,B$2:B$581)
and you would see the total paid by the person whose name is in A2 from all
entries on the sheet.

Lets say you also wanted to know how much the totals were for that same
person but from another sheet (with same basic column/row usage), then
=SUMIF(OtherSheet!A$2:A$581,A2,OtherSheet!B$2:B$58 1) would give you the
total from that other sheet for that person.

Put them together into a single cell and you'd get total from both sheets, as:
=SUMIF(A$2:A$581,A2,B$2:B$581) +
SUMIF(OtherSheet!A$2:A$581,A2,OtherSheet!B$2:B$581 )

To carry this one or two steps further so that you don't have to figure out
a way to create a list of unique names in a list, you can use COUNTIF() as a
filter.

Same formula, same 2 sheets involved and same 2 ranges, but you only want to
see the total the first time a name appears in column A:

=IF(COUNTIF($A$2:$A2)=1, SUMIF(A$2:A$581,A2,B$2:B$581) +
SUMIF(OtherSheet!A$2:A$581,A2,OtherSheet!B$2:B$581 ), "")
should do it for you. This formula can easily be filled down the sheet
through all rows with names on them on the sheet in question. The 'catch'
here is that if there is a name on the "other sheet" that isn't in the list
on this sheet, you won't get any payments for that person to show up at all.




"Jamie" wrote:

I have to wkshts with names and dollars paid, one for Jan to Jun, the second
for Jul to Dec. I need to find the names on both worksheets and add the
dollar amounts together. There is a formula that will help me put the names
together, I just can't remember, and am having no luck using the help button.
Can anyone give me a jump start?

Combining two worksheets into one
 

Yes and thank you. I am trying to take it to the next step with the last
formula you posted. I am getting the message 'too few arguments' and am
being refered to the criteria in the first string of the formula....
($A$2:$A2). i can't figure it out. Can you help again?

"JLatham" wrote:

I think that you're probably trying to remember SUMIF().

SUMIF() takes 3 parameters: (range to look through, what to match, range to
sum)
Lets say you have a bunch of names in column A and the amounts they paid in
column B. Your lists go from row 2 down to row 581.

In column C (or any unused column) at row 2 you could put a formula:
=SUMIF(A$2:A$581,A2,B$2:B$581)
and you would see the total paid by the person whose name is in A2 from all
entries on the sheet.

Lets say you also wanted to know how much the totals were for that same
person but from another sheet (with same basic column/row usage), then
=SUMIF(OtherSheet!A$2:A$581,A2,OtherSheet!B$2:B$58 1) would give you the
total from that other sheet for that person.

Put them together into a single cell and you'd get total from both sheets, as:
=SUMIF(A$2:A$581,A2,B$2:B$581) +
SUMIF(OtherSheet!A$2:A$581,A2,OtherSheet!B$2:B$581 )

To carry this one or two steps further so that you don't have to figure out
a way to create a list of unique names in a list, you can use COUNTIF() as a
filter.

Same formula, same 2 sheets involved and same 2 ranges, but you only want to
see the total the first time a name appears in column A:

=IF(COUNTIF($A$2:$A2)=1, SUMIF(A$2:A$581,A2,B$2:B$581) +
SUMIF(OtherSheet!A$2:A$581,A2,OtherSheet!B$2:B$581 ), "")
should do it for you. This formula can easily be filled down the sheet
through all rows with names on them on the sheet in question. The 'catch'
here is that if there is a name on the "other sheet" that isn't in the list
on this sheet, you won't get any payments for that person to show up at all.




"Jamie" wrote:

I have to wkshts with names and dollars paid, one for Jan to Jun, the second
for Jul to Dec. I need to find the names on both worksheets and add the
dollar amounts together. There is a formula that will help me put the names
together, I just can't remember, and am having no luck using the help button.
Can anyone give me a jump start?