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

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

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

=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

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

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

On Mar 28, 10:44*am, "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- Hide quoted text -

- Show quoted text -

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

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)

"Pete_UK" wrote in message
...
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

On Mar 28, 10:44 am, "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- Hide quoted text -

- Show quoted text -

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

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

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

"Teethless mama" wrote:

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





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 , so that i can work
tonight or else i have to check in the morning only. sorry for the
inconvenience.

best regards,

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

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

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

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

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

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

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

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.

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

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)

"T. Valko" wrote in message
...
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

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

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

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

Thanks

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

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.