I am trying to create a summary sheet of information from other worksheets
within the same workbook. Each month I download a list of names(column A),
with a code beside it (column B) and with an amount beside each one (column
C). The name may appear twice (each time having a different code). What I
would like to do is create a summary sheet ( Jan - Dec) that will pull the
info from each months sheet. So...it will pull the name once from column A
and give me a total amount from column C if it's the same name. However
because the amount of names increase or decrease each month I am unsure on
how to go about this.
example:
Worksheet 1 Worksheet 2 Summary Sheet
Jones EAH 5 Jones EAH 3 Jones 15
Jones ABC 7 Lent LOA 2 Lent 2
Mac EAH 2 Smith ABC 4 Mac 2
Smith EAH 9 Smith EAH 5 Smith 25
Smith LOA 7

any help would be appreciated

In your summary sheet

B2
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{1,2}&"!$A$2:$A $6"),"*"&A2&"*",INDIRECT("Sheet"&{1,2}&"!$B$2:$B$6 ")))

copy down from B2 to B4 on your summary sheet


"because" wrote:

I am trying to create a summary sheet of information from other worksheets
within the same workbook. Each month I download a list of names(column A),
with a code beside it (column B) and with an amount beside each one (column
C). The name may appear twice (each time having a different code). What I
would like to do is create a summary sheet ( Jan - Dec) that will pull the
info from each months sheet. So...it will pull the name once from column A
and give me a total amount from column C if it's the same name. However
because the amount of names increase or decrease each month I am unsure on
how to go about this.
example:
Worksheet 1 Worksheet 2 Summary Sheet
Jones EAH 5 Jones EAH 3 Jones 15
Jones ABC 7 Lent LOA 2 Lent 2
Mac EAH 2 Smith ABC 4 Mac 2
Smith EAH 9 Smith EAH 5 Smith 25
Smith LOA 7

any help would be appreciated

Thank you for the help...the formula you gave me does work great...except
that I just want a total in the summary sheet for the particular person in
the list....not the whole list.....is this possible? Thanks again!

"Teethless mama" wrote:

In your summary sheet

B2
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{1,2}&"!$A$2:$A $6"),"*"&A2&"*",INDIRECT("Sheet"&{1,2}&"!$B$2:$B$6 ")))

copy down from B2 to B4 on your summary sheet


"because" wrote:

I am trying to create a summary sheet of information from other worksheets
within the same workbook. Each month I download a list of names(column A),
with a code beside it (column B) and with an amount beside each one (column
C). The name may appear twice (each time having a different code). What I
would like to do is create a summary sheet ( Jan - Dec) that will pull the
info from each months sheet. So...it will pull the name once from column A
and give me a total amount from column C if it's the same name. However
because the amount of names increase or decrease each month I am unsure on
how to go about this.
example:
Worksheet 1 Worksheet 2 Summary Sheet
Jones EAH 5 Jones EAH 3 Jones 15
Jones ABC 7 Lent LOA 2 Lent 2
Mac EAH 2 Smith ABC 4 Mac 2
Smith EAH 9 Smith EAH 5 Smith 25
Smith LOA 7

any help would be appreciated

oh...and I want my summary sheet to pull the name from each sheet that
corresponds to the total pulled for them

"Teethless mama" wrote:

In your summary sheet

B2
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{1,2}&"!$A$2:$A $6"),"*"&A2&"*",INDIRECT("Sheet"&{1,2}&"!$B$2:$B$6 ")))

copy down from B2 to B4 on your summary sheet


"because" wrote:

I am trying to create a summary sheet of information from other worksheets
within the same workbook. Each month I download a list of names(column A),
with a code beside it (column B) and with an amount beside each one (column
C). The name may appear twice (each time having a different code). What I
would like to do is create a summary sheet ( Jan - Dec) that will pull the
info from each months sheet. So...it will pull the name once from column A
and give me a total amount from column C if it's the same name. However
because the amount of names increase or decrease each month I am unsure on
how to go about this.
example:
Worksheet 1 Worksheet 2 Summary Sheet
Jones EAH 5 Jones EAH 3 Jones 15
Jones ABC 7 Lent LOA 2 Lent 2
Mac EAH 2 Smith ABC 4 Mac 2
Smith EAH 9 Smith EAH 5 Smith 25
Smith LOA 7

any help would be appreciated