Index, Match, Sum??

[Betty Csehi]

I have a worksheet with ID numbers in Col A and $ amounts in Col B. There
are duplications of the ID numbers.

I also have another worksheet with a listing all of the ID numbers that I
want to use from the other worksheet mentioned above.

For example:
Worksheet A
Col A Col B
SD4 $500
SD8 $350
SD35 $426
SD8 $295
SD4 $100
SD12 $ 75
SD20 $ 50
SD35 $950
DS56 $238
(etc)

Worksheet B
Col A
SD4
SD8
SD12
SD20

In Col B of Worksheet B, I would like the total $ value for the cars listed
in Col A. (For example, SD4 would have a total in Col B of $600, SD8 would
be $645, SD12 would be $75, etc.)

Can anyone give me a formula to work with this? I am familiar with the
Index and Match functions, but I don't know how (or if) it can be used in
this example.

Thanks!

 

[Guest]

Try SUMIF

 

[Guest]

Try in col B of sheet2:

=sumproduct(--(sheet1!A2:A100=A2),--(Sheet1!B2:B100))

HTH

 

[Pete_UK]

In cell B1 of Worksheet B, enter this formula:

=SUMIF('Worksheet A'!A$1:A$100,A1,'Worksheet A'!B$1:B$100)

I've assumed you have 100 entries in worksheet A - adjust the cell
ranges as appropriate.

The formula can be copied down column B, to give you the totals from
sheet A against each ID number.

Hope this helps.

Pete

 

[Betty Csehi]

The SUMIF formula works - Thanks!

 

[Pete_UK]

Thanks for feeding back.

Pete