| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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, |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Most common occurrence of a string | Excel Worksheet Functions | |||
| Formula for searching for a text string | Excel Discussion (Misc queries) | |||
| Searching a text string in a range of cells. | Excel Worksheet Functions | |||
| Searching for string in other cells | Excel Worksheet Functions | |||
| searching and summing using string within cell | Excel Discussion (Misc queries) | |||
Linear Mode
