Sumproduct with a twist

[Pam]

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:A1000="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

 

[Duke Carey]

You'll need two SUMPRODUCTS

=SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A1000="Product

 

[JLatham]

For Revised Sales in J
=SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A1000="Product
A")*(O2:O1000="cc"),J2:J1000)
and for sales amount in I
= SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A1000="Product
A")*(O2:O1000<>"cc"),I2:I1000)

 

[Pam]

Duke,

Thanks so much for the solution. I never would have thought to add two
SUMPRODUCTS.

Thanks again,
Pam

 

[Steve Dunn]

Another option, untested but should work:

=SUMPRODUCT((TEXT(B2:B1000,"mmmyy")="Dec08")*(A2:A1000="Product A"),
(O2:O1000="cc")*J2:J1000+(O2:O1000<>"cc")*I2:I2000)

HTH
Steve.