sum on multiple criteria

[almagg]

i want to go thru a list and sum on the criteria that matches the first two letters.
in this particular case, sum if the first two letters are any of:
CN, KR, MY, NZ, SG, AU, HK, TW

ColG.............................................ColH
.
.
CN080: Marine-NSW........................1611
DE066: FPS Mobile Offenburg............1211
KR007: TSP Dongbang Plant..............17120
NZ032: Auckland-Wormald-Service.....8843
UK033: Frome, JW singer, UK.............2011
.
.

i tried this but then realized ALL of the criteria must be met.

=SUMIFS(G6:G1000,$F$6:$F$1000,"CN"&"*",$F$6:$F$1000,"KR"&"*",$F$6:$F$1000,"MY"&"*",$F$6:$F$1000,"NZ"&"*",$F$6:$F$1000,"SG"&"*",$F$6:$F$1000,"AU"&"*",$F$6:$F$1000,"HK"&"*",$F$6:$F$1000,"TW"&"*")

also tried a regular sumif suing OR

sumif(F6:F1000, OR("CN"&"*", "KR"&"*", "NZ"&"*"),G6:G1000)

any ideas?
thanks

 

[circledchicken]

Hi,

Maybe try:

=SUMPRODUCT((LEFT(F6:F1000,2)={"CN","KR","MY","NZ","SG","AU","HK","TW"})*(G6:G1000))

 

[Aladin Akyurek]

i want to go thru a list and sum on the criteria that matches the first two letters.
in this particular case, sum if the first two letters are any of:
CN, KR, MY, NZ, SG, AU, HK, TW

ColG.............................................ColH
.
.
CN080: Marine-NSW........................1611
DE066: FPS Mobile Offenburg............1211
KR007: TSP Dongbang Plant..............17120
NZ032: Auckland-Wormald-Service.....8843
UK033: Frome, JW singer, UK.............2011
.
.

i tried this but then realized ALL of the criteria must be met.

=SUMIFS(G6:G1000,$F$6:$F$1000,"CN"&"*",$F$6:$F$1000,"KR"&"*",
$F$6:$F$1000,"MY"&"*",$F$6:$F$1000,"NZ"&"*",$F$6:$F$1000,"SG"&"*",
$F$6:$F$1000,"AU"&"*",$F$6:$F$1000,"HK"&"*",$F$6:$F$1000,"TW"&"*")

also tried a regular sumif suing OR

sumif(F6:F1000, OR("CN"&"*", "KR"&"*", "NZ"&"*"),G6:G1000)

any ideas?
thanks

Try...

=SUM(SUMIF(F6:F1000, {"CN","KR","NZ"}&"*",G6:G1000))

 

[almagg]

thanks
they both worked.

the brackets i guess indicate an array?
just wondering how to know to use them in other cases.

 

[Aladin Akyurek]

thanks
they both worked.

the brackets i guess indicate an array?
just wondering how to know to use them in other cases.

{"CN","KR","NZ"} is called an array constant. If you would put the items in a 3-cell range and name List that range, you had to invoke:

=SUMPRODUCT(SUMIF(F6:F1000,List&"*",G6:G1000))

 

[almagg]

that is good to know.
thanks

 

[Aladin Akyurek]

that is good to know.
thanks

You are welcome.