Adding fileds in a list based on a lookup
 

I have a sheet containing 3 columns. The first column is a name, the second
and third are numbers. I would like to have a foumula that will look down the
list for each occurance of a specific name, and upon finding it, add the
contents of column 2 and 3 on that row to a running total for all of the
occurances found. Ex:ample:

| 1 | 2 | 3
----------------------------------
A | Gregory Day | 2 | 2
B | Gregory Day | 2 | 2
C | Gregory Day | 2 | 2

Should return an answer of 12.

Does that make sense? The only additional question is that the formula would
be a sheet 1, the data on sheet 2.

I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations
there of, with out luck. Any help would be greatly appreciated.

Thank you,

Adding fileds in a list based on a lookup
 

Try this version of SUMPRODUCT:

=SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5))
Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per
your request.

H.T.H.
--
when u change the way u look @ things, the things u look at change.


"Gregory Day" wrote:

I have a sheet containing 3 columns. The first column is a name, the second
and third are numbers. I would like to have a foumula that will look down the
list for each occurance of a specific name, and upon finding it, add the
contents of column 2 and 3 on that row to a running total for all of the
occurances found. Ex:ample:

| 1 | 2 | 3
----------------------------------
A | Gregory Day | 2 | 2
B | Gregory Day | 2 | 2
C | Gregory Day | 2 | 2

Should return an answer of 12.

Does that make sense? The only additional question is that the formula would
be a sheet 1, the data on sheet 2.

I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations
there of, with out luck. Any help would be greatly appreciated.

Thank you,

Adding fileds in a list based on a lookup
 

THANK YOU! That makes sense. Now, it just returns #NA. I am trying to
incorporate ISNA in to the formula you provided, but can't seem to make it
work.

Any suggestions on that?

-Thanks again.

"sahafi" wrote:

Try this version of SUMPRODUCT:

=SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5))
Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per
your request.

H.T.H.
--
when u change the way u look @ things, the things u look at change.


"Gregory Day" wrote:

I have a sheet containing 3 columns. The first column is a name, the second
and third are numbers. I would like to have a foumula that will look down the
list for each occurance of a specific name, and upon finding it, add the
contents of column 2 and 3 on that row to a running total for all of the
occurances found. Ex:ample:

| 1 | 2 | 3
----------------------------------
A | Gregory Day | 2 | 2
B | Gregory Day | 2 | 2
C | Gregory Day | 2 | 2

Should return an answer of 12.

Does that make sense? The only additional question is that the formula would
be a sheet 1, the data on sheet 2.

I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations
there of, with out luck. Any help would be greatly appreciated.

Thank you,

Adding fileds in a list based on a lookup
 

After working with this I found something I failed to mention. Not all rows
contain the reference criteria. In the example below, if the reference is
"Gregory Day" then the answer returned should be 8.

How do I make Excel add columns 2 and 3 to a running total for only the rows
that contain the reference in column 1?

| 1 | 2 | 3
----------------------------------
A | Gregory Day | 2 | 2
B | John Doe | 2 | 2
C | Gregory Day | 2 | 2

Thank you so much! All your help is greatly appreciated.

"sahafi" wrote:

Try this version of SUMPRODUCT:

=SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5))
Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per
your request.

H.T.H.
--
when u change the way u look @ things, the things u look at change.


"Gregory Day" wrote:

I have a sheet containing 3 columns. The first column is a name, the second
and third are numbers. I would like to have a foumula that will look down the
list for each occurance of a specific name, and upon finding it, add the
contents of column 2 and 3 on that row to a running total for all of the
occurances found. Ex:ample:

| 1 | 2 | 3
----------------------------------
A | Gregory Day | 2 | 2
B | Gregory Day | 2 | 2
C | Gregory Day | 2 | 2

Should return an answer of 12.

Does that make sense? The only additional question is that the formula would
be a sheet 1, the data on sheet 2.

I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations
there of, with out luck. Any help would be greatly appreciated.

Thank you,

Adding fileds in a list based on a lookup
 

Here's how you add the ISNA to trap the error:

=IF(ISNA(SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5))),"",SUMPROD UCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5)))

H.T.H.
--
when u change the way u look @ things, the things u look at change.


"Gregory Day" wrote:

THANK YOU! That makes sense. Now, it just returns #NA. I am trying to
incorporate ISNA in to the formula you provided, but can't seem to make it
work.

Any suggestions on that?

-Thanks again.

"sahafi" wrote:

Try this version of SUMPRODUCT:

=SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5))
Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per
your request.

H.T.H.
--
when u change the way u look @ things, the things u look at change.


"Gregory Day" wrote:

I have a sheet containing 3 columns. The first column is a name, the second
and third are numbers. I would like to have a foumula that will look down the
list for each occurance of a specific name, and upon finding it, add the
contents of column 2 and 3 on that row to a running total for all of the
occurances found. Ex:ample:

| 1 | 2 | 3
----------------------------------
A | Gregory Day | 2 | 2
B | Gregory Day | 2 | 2
C | Gregory Day | 2 | 2

Should return an answer of 12.

Does that make sense? The only additional question is that the formula would
be a sheet 1, the data on sheet 2.

I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations
there of, with out luck. Any help would be greatly appreciated.

Thank you,

Adding fileds in a list based on a lookup
 

I'm not sure I understood what you are asking. But the way SUMPRODUCT formula
works, it will look up the criteria (your name for example), then it will add
up all the coresponding numbers for that name on both columns 2 and 3. So the
formula is already doing that. If the criteria you are using to look up the
reference in Col1 doesn't exist, you will recieve that error (previous
message explained how to trap the error). So unless you clarify what you are
trying to accomplish, we will not be able to help you in that regard. Just
remember that on the sheet that contains the formula, you list the same
reference (only once) you have on col1 of your data sheet.
--
when u change the way u look @ things, the things u look at change.


"Gregory Day" wrote:

After working with this I found something I failed to mention. Not all rows
contain the reference criteria. In the example below, if the reference is
"Gregory Day" then the answer returned should be 8.

How do I make Excel add columns 2 and 3 to a running total for only the rows
that contain the reference in column 1?

| 1 | 2 | 3
----------------------------------
A | Gregory Day | 2 | 2
B | John Doe | 2 | 2
C | Gregory Day | 2 | 2

Thank you so much! All your help is greatly appreciated.

"sahafi" wrote:

Try this version of SUMPRODUCT:

=SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5))
Assuming the data is in Sheet2 A1:C5 and the formula goes into Sheet2 per
your request.

H.T.H.
--
when u change the way u look @ things, the things u look at change.


"Gregory Day" wrote:

I have a sheet containing 3 columns. The first column is a name, the second
and third are numbers. I would like to have a foumula that will look down the
list for each occurance of a specific name, and upon finding it, add the
contents of column 2 and 3 on that row to a running total for all of the
occurances found. Ex:ample:

| 1 | 2 | 3
----------------------------------
A | Gregory Day | 2 | 2
B | Gregory Day | 2 | 2
C | Gregory Day | 2 | 2

Should return an answer of 12.

Does that make sense? The only additional question is that the formula would
be a sheet 1, the data on sheet 2.

I have tried, SUM, SUMPRODUCT, COUNT, COUNTIF, AVERAGE and combinations
there of, with out luck. Any help would be greatly appreciated.

Thank you,