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

Sumproduct with a twist
 

You'll need two SUMPRODUCTS

=SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A 1000="Product
A"),--(o2:o1000="cc"),j2:j1000)+=SUMPRODUCT((TEXT(B2:B10 00,"mmmyy")="Dec08")*(A2:A1000="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


.

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


.

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"),--(o2:o1000="cc"),j2:j1000)+=SUMPRODUCT((TEXT(B2:B10 00,"mmmyy")="Dec08")*(A2:A1000="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


.

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