You would do far better to break it down.

For the sake of argument, assume DREGION is in D2:D3000 and DUESMIS is in
H2:H3000, then in say M2 add

=IF(ISNUMBER(MATCH(D2,P25:P28,0)),H2,0)

Copy that down then do a simple sum at the bottom, or wherever you want the
sum

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Eddy Stan" wrote in message
...


"T. Valko" wrote:

Even shorter:

Assuming DREGION and MISDUE are vertical arrays...

A1:C1 = FREIGHT; TRANSPORT; HQ BLNG

=SUMPRODUCT((ISNUMBER(SEARCH(A1:C1,DREGION)))*MISD UE)


--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
even shorter

=SUMPRODUCT((ISNUMBER(SEARCH({"HQ
BLNG","TRANSPORT","FREIGHT"},DREGION)))*MISDUE)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Teethless mama" wrote in
message ...
Shorter version:

=SUMPRODUCT(--(ISNUMBER(SEARCH({"HQ
BLNG","TRANSPORT","FREIGHT"},DREGION)))*MISDUE)



"Bob Phillips" wrote:

=SUMPRODUCT(--(ISNUMBER(SEARCH("HQ BLNG",DREGION)))+
(ISNUMBER(SEARCH("TRANSPORT",DREGION)))+

(ISNUMBER(SEARCH("FREIGHT",DREGION))),MISDUE)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Eddy Stan" wrote in message
...
Hi
Can anyone help me on this. thanks in advance.
I am getting answer required by the following formula
=SUMPRODUCT(--(DREGION="HQ BLNG EAST")+(DREGION="HQ BLNG
WEST")+(DREGION="HQ
BLNG NORTH")+(DREGION="HQ BLNG SOUTH"),MISDUE)
Where the first part is constant "HQ BLNG".
Is there any way to search the string in range DREGION and find the
sum.
Like "HQ BLNG xxxx", "xxxx TRANSPORT xxxx", "xxxxx xxxxx FREIGHT"
xxxx i mean to say that it could be any string.

Thank you


Dear Bob - I get the Value error again.
=SUMPRODUCT((ISNUMBER(SEARCH({"HQ
BLNG","TRANSPORT","FREIGHT"},DREGION)))*DUESMIS)
Can you please try with an example.

Dear Valco,
(dregion & duesmis are same size parralel vertical single arrays)
Thanks for the try, the range option is still good. But I get Value error
=SUMPRODUCT((ISNUMBER(SEARCH(P25:P28,DREGION)))*DU ESMIS)
- where p25:p28 (are HQ BLNG, TRANSPORT, FREIGHT, WAREHOUSING)
Can you please try with an example.

I am applying this formula over 30,000 rows, of 42mb file, is no.of rows
the
problem ?
please advise.