Looking up multiple values for a single name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Aaron Dyck - 21 Jul 2006 12:48 GMT

I am trying to look up a name and return a sum of all values in one column
associated with that name. There are between one and five values for each
name. I am only able to return one value for each name, rather than the sum
of all the values. Is there a way to look up all instances of an item and
return the sum of the corresponding values?

Reply to this Message

Bondi - 21 Jul 2006 13:19 GMT

Aaron Dyck skrev:

> I am trying to look up a name and return a sum of all values in one column
> associated with that name. There are between one and five values for each
> name. I am only able to return one value for each name, rather than the sum
> of all the values. Is there a way to look up all instances of an item and
> return the sum of the corresponding values?

Hi Aaron,

With the information given maybe you can use the SUMPRODUCT()
Maybe something like:
=SUMPRODUCT(--(A1:A5="Name"),B1:B5)
If your names are in column A and values in column B

Maybe the information on this site will be helpful:
http://www.contextures.com/xlFunctions01.html#SumProduct

Regards,
Bondi

Reply to this Message

bj - 21 Jul 2006 13:20 GMT

try
=sumif(criteria range,criteria,datarange)

Reply to this Message

Aaron Dyck - 21 Jul 2006 13:55 GMT

Sorry, perhaps I should have made myself a little clearer. I am trying to
look up a value in the first column of another sheet, and return the sum of
the values of the 30th cell in each corresponding row.

What I've got right now is a =vlookup(Name,DataRange,30), but this only
gives me the first instance, and I need to sum up all instances.

> try
> =sumif(criteria range,criteria,datarange)
[quoted text clipped - 4 lines]
> > of all the values. Is there a way to look up all instances of an item and
> > return the sum of the corresponding values?

Reply to this Message

bj - 21 Jul 2006 19:34 GMT

Sumif should still work
=SUMIF(Sheet2!A1:A5,"a",Sheet2!AE1:AE5)
will work on sheet 1 if what you were looking for was an "a"

> Sorry, perhaps I should have made myself a little clearer. I am trying to
> look up a value in the first column of another sheet, and return the sum of
[quoted text clipped - 11 lines]
> > > of all the values. Is there a way to look up all instances of an item and
> > > return the sum of the corresponding values?

Reply to this Message