If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sumproduct with a twist
Hi,
Per code below supplied by Max in post dated 1/1/09 "Calculating the sum of different categories over a given time period", I need a slight variation that I can't seem to figure out. =SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A"),C2:C1000) I have a date field and a product field, but I also have two columns for sales amounts. If column O = cc, I want to total column J (revised sales amount). If column O cc, I want to total column I (sales amount). How do accommodate for this with sumproduct? Thanks in advance for any help. Pam |
#2
|
|||
|
|||
Sumproduct with a twist
You'll need two SUMPRODUCTS
=SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A"),--(o21000="cc"),j2:j1000)+=SUMPRODUCT((TEXT(B2:B10 00,"mmmyy")="Dec08")*(A2:A1000="Product A"),--(o21000"cc"),i2:i1000) "Pam" wrote: Hi, Per code below supplied by Max in post dated 1/1/09 "Calculating the sum of different categories over a given time period", I need a slight variation that I can't seem to figure out. =SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A"),C2:C1000) I have a date field and a product field, but I also have two columns for sales amounts. If column O = cc, I want to total column J (revised sales amount). If column O cc, I want to total column I (sales amount). How do accommodate for this with sumproduct? Thanks in advance for any help. Pam . |
#3
|
|||
|
|||
Sumproduct with a twist
For Revised Sales in J
=SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A")*(O2:O1000="cc"),J2:J1000) and for sales amount in I = SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A1 000="Product A")*(O2:O1000"cc"),I2:I1000) "Pam" wrote: Hi, Per code below supplied by Max in post dated 1/1/09 "Calculating the sum of different categories over a given time period", I need a slight variation that I can't seem to figure out. =SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A"),C2:C1000) I have a date field and a product field, but I also have two columns for sales amounts. If column O = cc, I want to total column J (revised sales amount). If column O cc, I want to total column I (sales amount). How do accommodate for this with sumproduct? Thanks in advance for any help. Pam . |
#4
|
|||
|
|||
Sumproduct with a twist
Duke,
Thanks so much for the solution. I never would have thought to add two SUMPRODUCTS. Thanks again, Pam "Duke Carey" wrote in message ... You'll need two SUMPRODUCTS =SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A"),--(o21000="cc"),j2:j1000)+=SUMPRODUCT((TEXT(B2:B10 00,"mmmyy")="Dec08")*(A2:A1000="Product A"),--(o21000"cc"),i2:i1000) "Pam" wrote: Hi, Per code below supplied by Max in post dated 1/1/09 "Calculating the sum of different categories over a given time period", I need a slight variation that I can't seem to figure out. =SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A"),C2:C1000) I have a date field and a product field, but I also have two columns for sales amounts. If column O = cc, I want to total column J (revised sales amount). If column O cc, I want to total column I (sales amount). How do accommodate for this with sumproduct? Thanks in advance for any help. Pam . |
#5
|
|||
|
|||
Sumproduct with a twist
Another option, untested but should work:
=SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A"), (O2:O1000="cc")*J2:J1000+(O2:O1000"cc")*I2:I2000 ) HTH Steve. "Pam" wrote in message ... Hi, Per code below supplied by Max in post dated 1/1/09 "Calculating the sum of different categories over a given time period", I need a slight variation that I can't seem to figure out. =SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product A"),C2:C1000) I have a date field and a product field, but I also have two columns for sales amounts. If column O = cc, I want to total column J (revised sales amount). If column O cc, I want to total column I (sales amount). How do accommodate for this with sumproduct? Thanks in advance for any help. Pam |
Thread Tools | |
Display Modes | |
|
|