help on sumproduct() - sum by searching for a common sub string in

[Eddy Stan]

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

 

[Bob Phillips]

=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)

 

[Pete_UK]

Hi Bob,

tried to access your site a few times recently and am told "The domain
xldynamic.com has expired". I then get put through to Xldynamic.com -
totally different. What's happening? Are you aware of this?

Pete

 

[Bob Phillips]

Hi Pete,

Yes, someone else told me this today. I am on the case.

Thanks

--
HTH

Bob

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

Hi Bob,

tried to access your site a few times recently and am told "The domain
xldynamic.com has expired". I then get put through to Xldynamic.com -
totally different. What's happening? Are you aware of this?

Pete

 

[Teethless mama]

Shorter version:

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

 

[Eddy Stan]

Shorter version:

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

Hi Bob - Thank you so much - the function worked very well.

Hi Teethless Mama - Thanks for the try. It gives me Value error, can you
please try again for me.
Can u please send the code in (e-mail address removed), so that i can work
tonight or else i have to check in the morning only. sorry for the
inconvenience.

best regards,

 

[Bob Phillips]

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)

 

[T. Valko]

Even shorter:

Assuming DREGION and MISDUE are vertical arrays...

A1:C1 = FREIGHT; TRANSPORT; HQ BLNG

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

 

[Eddy Stan]

Even shorter:

Assuming DREGION and MISDUE are vertical arrays...

A1:C1 = FREIGHT; TRANSPORT; HQ BLNG

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

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(P2528,DREGION)))*DUESMIS)
- where p2528 (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.

 

[Eddy Stan]

to remind again...
the search string may be anywhere in each cell of dregion range.
for example - "Transport" is prefixed by location name in 3 letters suffix
by 3 letter salesperson, with space in between.
That is why i put xxxx before / after search string in my query.

 

[Bob Phillips]

I think you are missing the point. I wasn't really trying to get the
shortest version, such formula pyrotechnics is largely pointless IMO, the
technically inverse equivalent of logorrhoea. TM made an 'improvement' on
mine, making the point that it was shorter, and included a superfluous --,
so I was ribbing him!

--
HTH

Bob

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

 

[Eddy Stan]

:

Even shorter:

Assuming DREGION and MISDUE are vertical arrays...

A1:C1 = FREIGHT; TRANSPORT; HQ BLNG

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

can you please work on the function you gave with sample and try to get me
error free function.

Thanks

 

[Bob Phillips]

You would do far better to break it down.

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

=IF(ISNUMBER(MATCH(D2,P2528,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)