Querying multiple Product Codes in one field using SUMPRODUCT()
 

Hi All,

I have a field named Product_Code in my database. Have another field called
country_code, and a last one named Shipment_Amt$.

My draft formula is:
=sumproduct(--(Product_code=B15), --(Country_code=$C$2))

How do i perfect my sumproduct formula to extract out multiple product
codes, look for a particular country code and sum up all the shipment amt?
Thanks...

Querying multiple Product Codes in one field using SUMPRODUCT()
 

try
=sumproduct(--(Product_code=B15), --(Country_code={$C$2,$c$5}),shipmentamt)


--
Don Guillett
SalesAid Software

"Tan" wrote in message
...
Hi All,

I have a field named Product_Code in my database. Have another field
called
country_code, and a last one named Shipment_Amt$.

My draft formula is:
=sumproduct(--(Product_code=B15), --(Country_code=$C$2))

How do i perfect my sumproduct formula to extract out multiple product
codes, look for a particular country code and sum up all the shipment amt?
Thanks...

Querying multiple Product Codes in one field using SUMPRODUCT()
 

Array constants *don't* allow cell references.

Say the product codes you're looking for are entered in B15 to B20.

Try this *array* formula:

=SUMPRODUCT((Product_Code=TRANSPOSE(INDIRECT("B15: B20")))*(Country_Code=C2)*
Shipment_Amts)

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Don Guillett" wrote in message
...
try

=sumproduct(--(Product_code=B15), --(Country_code={$C$2,$c$5}),shipmentamt)


--
Don Guillett
SalesAid Software

"Tan" wrote in message
...
Hi All,

I have a field named Product_Code in my database. Have another field
called
country_code, and a last one named Shipment_Amt$.

My draft formula is:
=sumproduct(--(Product_code=B15), --(Country_code=$C$2))

How do i perfect my sumproduct formula to extract out multiple product
codes, look for a particular country code and sum up all the shipment
amt?
Thanks...