Lookup & Addition help please.

H

h0l1yw0od

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.:)
 
G

Guest

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.
 
S

Sloth

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).
 
M

Mariano

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.
 
Top