Hi,

Does anyone know of a formula which would basically do the same as a
lookup but it instead of showing the amount that corresponds with the
name, it would find every occurrence of that name and add up all the
values in to a single cell? I've been looking for several hours and
tried different things (sumif, lookup, vlookup etc.) but have not found
the one that works.

I'd appreciate it if someone could put me out of my misery!

Thanks.:)


--
h0l1yw0od
------------------------------------------------------------------------
h0l1yw0od's Profile: http://www.excelforum.com/member.php...o&userid=28989
View this thread: http://www.excelforum.com/showthread...hreadid=505332

Hi

One way is to use SUMPRODUCT, something like this:
=SUMPRODUCT(--(A2:A100="blah"),--(B2:B100))
This function cannot use full column references, and the references must be
the same size.

Hope this helps.
Andy.

"h0l1yw0od" wrote
in message ...

Hi,

Does anyone know of a formula which would basically do the same as a
lookup but it instead of showing the amount that corresponds with the
name, it would find every occurrence of that name and add up all the
values in to a single cell? I've been looking for several hours and
tried different things (sumif, lookup, vlookup etc.) but have not found
the one that works.

I'd appreciate it if someone could put me out of my misery!

Thanks.:)


--
h0l1yw0od
------------------------------------------------------------------------
h0l1yw0od's Profile:
http://www.excelforum.com/member.php...o&userid=28989
View this thread: http://www.excelforum.com/showthread...hreadid=505332

SUMIF should work for you. Just make sure each variable is correct.

Example1:
=SUMIF(A1:A10,"=correct",B1:B10)
Example2:
=SUMIF(A1:A10,"12",B1:B10)

it goes like this
=SUMIF(range,test,sumrange)
range is the range you are comparing.
test is the test (must be in quotation marks).
sumrange is the actual range you are summing (omit if sumrang is range).

"h0l1yw0od" wrote:


Hi,

Does anyone know of a formula which would basically do the same as a
lookup but it instead of showing the amount that corresponds with the
name, it would find every occurrence of that name and add up all the
values in to a single cell? I've been looking for several hours and
tried different things (sumif, lookup, vlookup etc.) but have not found
the one that works.

I'd appreciate it if someone could put me out of my misery!

Thanks.:)


--
h0l1yw0od
------------------------------------------------------------------------
h0l1yw0od's Profile: http://www.excelforum.com/member.php...o&userid=28989
View this thread: http://www.excelforum.com/showthread...hreadid=505332

no idea if this is the easiest way to do it, but i have done this before and
it worked.
I would create subtotals in my lookup range, then vlookup the concatenate of
the cell and " subtotal"
that should work.

"h0l1yw0od" wrote:


Hi,

Does anyone know of a formula which would basically do the same as a
lookup but it instead of showing the amount that corresponds with the
name, it would find every occurrence of that name and add up all the
values in to a single cell? I've been looking for several hours and
tried different things (sumif, lookup, vlookup etc.) but have not found
the one that works.

I'd appreciate it if someone could put me out of my misery!

Thanks.:)


--
h0l1yw0od
------------------------------------------------------------------------
h0l1yw0od's Profile: http://www.excelforum.com/member.php...o&userid=28989
View this thread: http://www.excelforum.com/showthread...hreadid=505332