Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match, Sum??
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match, Sum??
Try SUMIF
"Betty Csehi" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match, Sum??
Try in col B of sheet2:
=sumproduct(--(sheet1!A2:A100=A2),--(Sheet1!B2:B100)) HTH "Betty Csehi" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match, Sum??
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match, Sum??
The SUMIF formula works - Thanks!
"Betty Csehi" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index, Match, Sum??
Thanks for feeding back.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using AutoFilter to Sort Data Pulled in by INDEX / MATCH | Excel Worksheet Functions | |||
Match Index | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |