Querying multiple Product Codes in one field using SUMPRODUCT()

[Tan]

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...

 

[Don Guillett]

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

 

[Ragdyer]

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 !
---------------------------------------------------------------------------

try

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