| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky Formula
Hello,
I am not sure if this can be done or not, but I am looking to find a formula or something to sum values of a list, but only sum if there is more than one product. Here is the example: I want to add 60971, 60975, 60982 dollar amounts to get a grand total, but I don't want to include 61097 or 60972 or 09710 because there is only 1 item each. I tried to use a pivot table but got stuck on excluding the 61097 and 60972 and 09710 amounts. Item Number Amount Base Number 61097-9Z $7.00 61097 60971-115Z $14.00 60971 60971-115A $2.00 60971 09710-85A $14.00 09710 60971-8Z $5.00 60971 60972-BRN $2.00 60972 60975-75A $3.00 60975 60975-85A $3.00 60975 609753-9A $16.00 60975 60982-3032 $9.75 60982 60982-3230 $9.75 60982 60982-3232 $7.00 60982 Any help you can give is great! Thanks, Steph |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Formula
Try something like this:
With your data list in A1:C13 F1: =SUMPRODUCT(SUMIF(C1:C13,{60971,60975,60982},B1:B1 3)) OR If you enter 60971, 60975, and 60982 in cells E1:E3 F1: =SUMPRODUCT(SUMIF(C1:C13,E1:E3,B1:B13)) Does that help? *********** Regards, Ron XL2002, WinXP "steph44haf" wrote: Hello, I am not sure if this can be done or not, but I am looking to find a formula or something to sum values of a list, but only sum if there is more than one product. Here is the example: I want to add 60971, 60975, 60982 dollar amounts to get a grand total, but I don't want to include 61097 or 60972 or 09710 because there is only 1 item each. I tried to use a pivot table but got stuck on excluding the 61097 and 60972 and 09710 amounts. Item Number Amount Base Number 61097-9Z $7.00 61097 60971-115Z $14.00 60971 60971-115A $2.00 60971 09710-85A $14.00 09710 60971-8Z $5.00 60971 60972-BRN $2.00 60972 60975-75A $3.00 60975 60975-85A $3.00 60975 609753-9A $16.00 60975 60982-3032 $9.75 60982 60982-3230 $9.75 60982 60982-3232 $7.00 60982 Any help you can give is great! Thanks, Steph |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Formula
Ron,
That is a good formula, but the data set is actually a lot larger than what I provided and I would not have the time to type in the different number. I was hoping the formula could identify those that have more than 1 item listed and then from there sum the amounts. "Ron Coderre" wrote: Try something like this: With your data list in A1:C13 F1: =SUMPRODUCT(SUMIF(C1:C13,{60971,60975,60982},B1:B1 3)) OR If you enter 60971, 60975, and 60982 in cells E1:E3 F1: =SUMPRODUCT(SUMIF(C1:C13,E1:E3,B1:B13)) Does that help? *********** Regards, Ron XL2002, WinXP "steph44haf" wrote: Hello, I am not sure if this can be done or not, but I am looking to find a formula or something to sum values of a list, but only sum if there is more than one product. Here is the example: I want to add 60971, 60975, 60982 dollar amounts to get a grand total, but I don't want to include 61097 or 60972 or 09710 because there is only 1 item each. I tried to use a pivot table but got stuck on excluding the 61097 and 60972 and 09710 amounts. Item Number Amount Base Number 61097-9Z $7.00 61097 60971-115Z $14.00 60971 60971-115A $2.00 60971 09710-85A $14.00 09710 60971-8Z $5.00 60971 60972-BRN $2.00 60972 60975-75A $3.00 60975 60975-85A $3.00 60975 609753-9A $16.00 60975 60982-3032 $9.75 60982 60982-3230 $9.75 60982 60982-3232 $7.00 60982 Any help you can give is great! Thanks, Steph |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Formula
Then....how about this:
=SUMPRODUCT((COUNTIF(C2:C13,C2:C13)1)*B2:B13) Does that help? *********** Regards, Ron XL2002, WinXP "steph44haf" wrote: Ron, That is a good formula, but the data set is actually a lot larger than what I provided and I would not have the time to type in the different number. I was hoping the formula could identify those that have more than 1 item listed and then from there sum the amounts. "Ron Coderre" wrote: Try something like this: With your data list in A1:C13 F1: =SUMPRODUCT(SUMIF(C1:C13,{60971,60975,60982},B1:B1 3)) OR If you enter 60971, 60975, and 60982 in cells E1:E3 F1: =SUMPRODUCT(SUMIF(C1:C13,E1:E3,B1:B13)) Does that help? *********** Regards, Ron XL2002, WinXP "steph44haf" wrote: Hello, I am not sure if this can be done or not, but I am looking to find a formula or something to sum values of a list, but only sum if there is more than one product. Here is the example: I want to add 60971, 60975, 60982 dollar amounts to get a grand total, but I don't want to include 61097 or 60972 or 09710 because there is only 1 item each. I tried to use a pivot table but got stuck on excluding the 61097 and 60972 and 09710 amounts. Item Number Amount Base Number 61097-9Z $7.00 61097 60971-115Z $14.00 60971 60971-115A $2.00 60971 09710-85A $14.00 09710 60971-8Z $5.00 60971 60972-BRN $2.00 60972 60975-75A $3.00 60975 60975-85A $3.00 60975 609753-9A $16.00 60975 60982-3032 $9.75 60982 60982-3230 $9.75 60982 60982-3232 $7.00 60982 Any help you can give is great! Thanks, Steph |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Tricky Formula
Yes, this is exactly what I needed. Thank you! Thank you!
"Ron Coderre" wrote: Then....how about this: =SUMPRODUCT((COUNTIF(C2:C13,C2:C13)1)*B2:B13) Does that help? *********** Regards, Ron XL2002, WinXP "steph44haf" wrote: Ron, That is a good formula, but the data set is actually a lot larger than what I provided and I would not have the time to type in the different number. I was hoping the formula could identify those that have more than 1 item listed and then from there sum the amounts. "Ron Coderre" wrote: Try something like this: With your data list in A1:C13 F1: =SUMPRODUCT(SUMIF(C1:C13,{60971,60975,60982},B1:B1 3)) OR If you enter 60971, 60975, and 60982 in cells E1:E3 F1: =SUMPRODUCT(SUMIF(C1:C13,E1:E3,B1:B13)) Does that help? *********** Regards, Ron XL2002, WinXP "steph44haf" wrote: Hello, I am not sure if this can be done or not, but I am looking to find a formula or something to sum values of a list, but only sum if there is more than one product. Here is the example: I want to add 60971, 60975, 60982 dollar amounts to get a grand total, but I don't want to include 61097 or 60972 or 09710 because there is only 1 item each. I tried to use a pivot table but got stuck on excluding the 61097 and 60972 and 09710 amounts. Item Number Amount Base Number 61097-9Z $7.00 61097 60971-115Z $14.00 60971 60971-115A $2.00 60971 09710-85A $14.00 09710 60971-8Z $5.00 60971 60972-BRN $2.00 60972 60975-75A $3.00 60975 60975-85A $3.00 60975 609753-9A $16.00 60975 60982-3032 $9.75 60982 60982-3230 $9.75 60982 60982-3232 $7.00 60982 Any help you can give is great! Thanks, Steph |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Formula to show zero if reference cell is zero? tricky! | Excel Worksheet Functions | |||
| Help with a tricky formula...... | Excel Discussion (Misc queries) | |||
| Tricky formula | Excel Discussion (Misc queries) | |||
| "Unable to set the Formula property of the Series class" with a tw | Charts and Charting in Excel | |||
Linear Mode
