Add multiple vlookup results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dawn - 20 Jun 2006 19:06 GMT

I have a spreadsheet that contains names of groups and numerical results for
them as below.
Column A Column B
Frame .50
Build .25
Assy 1 .33
Build 1.5

I need to write a formula that will look for all the occurances of "Build"
and add the numerical amounts together. I've been trying to do it with
vlookup, but maybe that's not the best choice. Does anyone know the best way
to do this?

Thanks!

Reply to this Message

Vito - 20 Jun 2006 19:15 GMT

Try

=Sumif(A2:A10,"Build",B2:B10)

adjust ranges to suit.

Signature

Vito

Reply to this Message

Marcelo - 20 Jun 2006 19:21 GMT

Hi Dawn

try this

=SUMPRODUCT(--(A2:A5="Build"),(B2:B5))

HTH
Regards from Brazil
Marcelo

> I have a spreadsheet that contains names of groups and numerical results for
> them as below.
[quoted text clipped - 10 lines]
>
> Thanks!

Reply to this Message

Franz Verga - 20 Jun 2006 19:23 GMT

Nel post news:759C0A5F-13A2-42CC-A8F7-1D17CADBDFEE@microsoft.com
*Dawn* ha scritto:

> I have a spreadsheet that contains names of groups and numerical
> results for them as below.
[quoted text clipped - 10 lines]
>
> Thanks!

I think th best way should by the function SUMIF:

=SUMIF(A2:A4,"Build",B2:B4)

Signature

Ciao

Franz Verga from Italy

Reply to this Message

Dawn - 20 Jun 2006 21:12 GMT

Thanks to all of you! However, I don't think I explained it very well. I
actually have two spreadsheets; the first as detailed below, and the second
one looks like this.

Column A Column B
Frame XXX
Assy 1 XXX
Build XXX
Assy 2 XXX
Etc

In the second worksheet, I need to create a formula that will look for each
item in Column A in the first worksheet and populate Column B with the amount
listed next to the matched Department. Vlookup works very well for all
except the Build one because it has multiple entries in the first worksheet
that need to be added together. I'm sorry I didn't explain this better the
first time. I just figured you all knew what was in my head!! :)

Thanks!!

> Nel post news:759C0A5F-13A2-42CC-A8F7-1D17CADBDFEE@microsoft.com
> *Dawn* ha scritto:
[quoted text clipped - 17 lines]
>
> =SUMIF(A2:A4,"Build",B2:B4)

Reply to this Message

Marcelo - 20 Jun 2006 21:32 GMT

OK Dawn,

Assuming your data is on the WS 2 A2:B1000

on the WS1 use the formula
=sumproduct(--(ws2!$A$2:$A$1000=A2),(WS2!$B$2:$B$1000))

hope this helps
Regards from Brazil
Marcelo

> Thanks to all of you! However, I don't think I explained it very well. I
> actually have two spreadsheets; the first as detailed below, and the second
[quoted text clipped - 37 lines]
> >
> > =SUMIF(A2:A4,"Build",B2:B4)

Reply to this Message

Franz Verga - 20 Jun 2006 22:06 GMT

Nel post news:18B54FF2-FF4B-46CE-A5DF-9E678D9A31F7@microsoft.com
*Dawn* ha scritto:

> Thanks to all of you! However, I don't think I explained it very
> well. I actually have two spreadsheets; the first as detailed below,
[quoted text clipped - 16 lines]
>
> Thanks!!

You have just to modify the formula; put this formula in B2 of your second
sheet and fill down:

=SUMIF(Sheet1!A2:A4,A2,Sheet1!B2:B4)

Signature

Ciao

Franz Verga from Italy

Reply to this Message