Adding fileds in a list based on a lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gregory Day - 25 Mar 2008 22:55 GMT

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,

Reply to this Message

sahafi - 25 Mar 2008 23:28 GMT

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.

Signature

when u change the way u look @ things, the things u look at change.

> 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
[quoted text clipped - 17 lines]
>
> Thank you,

Reply to this Message

Gregory Day - 26 Mar 2008 13:57 GMT

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.

> Try this version of SUMPRODUCT:
>
[quoted text clipped - 25 lines]
> >
> > Thank you,

Reply to this Message

sahafi - 30 Mar 2008 17:27 GMT

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))),"",SUMPRODUCT(--(Sheet2!$A$1:$A$5=Sheet1!A1),--(Sheet2!$B$1:$B$5)+(Sheet2!$C$1:$C$5)))

H.T.H.

Signature

when u change the way u look @ things, the things u look at change.

> 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
[quoted text clipped - 33 lines]
> > >
> > > Thank you,

Reply to this Message

Gregory Day - 26 Mar 2008 17:17 GMT

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.

> Try this version of SUMPRODUCT:
>
[quoted text clipped - 25 lines]
> >
> > Thank you,

Reply to this Message

sahafi - 30 Mar 2008 17:35 GMT

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.

Signature

when u change the way u look @ things, the things u look at change.

> 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
[quoted text clipped - 40 lines]
> > >
> > > Thank you,

Reply to this Message