Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search For Multiple Entries - Combine and Sum
Hi Folks, Hoping I could get some guidance with this:
I have 3 columns of data on sheet1. Column A is numerical "APN" Column B is text "Product Description" Column C is numerical "Total" Is there a way to search column A for multiple matching entries and if so to sum the total while reducing back to one entry? EG: SHEET CURRENTLY LOOKS LIKE THIS APN Product Description Total 25454 AAA 2 32121 BBB 1 32654 CCC 2 25454 AAA 5 WOULD LIKE IT TO DO THIS APN Product Description Total 25454 AAA 7 32121 BBB 1 32654 CCC 2 This one is beyond my skills and would appreciate any assistance. Thanks for your time. Dean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search For Multiple Entries - Combine and Sum
a sumproduct function should do it
=sumproduct((a2:a22=25454)*(b2:b22="a")*c2:c22) -- Don Guillett SalesAid Software "Ledge" wrote in message oups.com... Hi Folks, Hoping I could get some guidance with this: I have 3 columns of data on sheet1. Column A is numerical "APN" Column B is text "Product Description" Column C is numerical "Total" Is there a way to search column A for multiple matching entries and if so to sum the total while reducing back to one entry? EG: SHEET CURRENTLY LOOKS LIKE THIS APN Product Description Total 25454 AAA 2 32121 BBB 1 32654 CCC 2 25454 AAA 5 WOULD LIKE IT TO DO THIS APN Product Description Total 25454 AAA 7 32121 BBB 1 32654 CCC 2 This one is beyond my skills and would appreciate any assistance. Thanks for your time. Dean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search For Multiple Entries - Combine and Sum
Dean,
=SUMPRODUCT(--(A2:A5=25454),--(C2:C5)) Or =SUMPRODUCT(--(A2:A5=G2),--(C2:C5)) where G2=25454 So if you have a list of all APN and Descriptions in a TOTAL sheet and your other multiple entry data on a DATA sheet then you could use this for each APN in your TOTAL sheet and copy down. =SUMPRODUCT(--(Data!A2:A100=Total!A2),--(Data!C2:100)) Change ranges to suit HTH "Ledge" wrote: Hi Folks, Hoping I could get some guidance with this: I have 3 columns of data on sheet1. Column A is numerical "APN" Column B is text "Product Description" Column C is numerical "Total" Is there a way to search column A for multiple matching entries and if so to sum the total while reducing back to one entry? EG: SHEET CURRENTLY LOOKS LIKE THIS APN Product Description Total 25454 AAA 2 32121 BBB 1 32654 CCC 2 25454 AAA 5 WOULD LIKE IT TO DO THIS APN Product Description Total 25454 AAA 7 32121 BBB 1 32654 CCC 2 This one is beyond my skills and would appreciate any assistance. Thanks for your time. Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine: ISNUMBER(SEARCH( and EXACT( ? | Excel Discussion (Misc queries) | |||
Combine Multiple Entries with differing amounts of entries | Excel Worksheet Functions | |||
Can I compare 2 lists to combine duplicate entries in new list? | Excel Worksheet Functions | |||
vlookup and search combine in vba. | Excel Programming | |||
vlookup and search combine in vba. | Excel Programming |