|
sum wrt substrings!
hi! i am having the following data in A1:B11 COL"A" COL"B" aabbccc 100 aabbcccaa 500 aabbxyz 50 aabbxyzab 200 aabbcccxy 300 aabbzzz 200 aabbzzzab 500 aabbxyzac 700 aabbyyyca 100 aabbcccda 800 aabbdddab 900 how can i get the sum of COL "B" wrt the substring of COL "A'? ie., the sum of substrings "ccc", "xyz", "zzz", "yyy", "ccc", & "ddd" of COL "A" , assuming that the substring always starts from the 5th character of COL "A"..!! hope i have detailed the things enough??!!! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 |
sum wrt substrings!
Do you want the total for all these substrings?
=SUM(SUMIF(A:A,"*"&{"ccc";"xyz";"zzz";"yyy";"ddd"} &"*",B:B)) for zzz only would look like =SUM(SUMIF(A:A,"*zzz*",B:B)) or even =SUM(SUMIF(A:A,"*"&D1&"*",B:B)) where you would put the substring in D1 -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "via135" wrote in message ... hi! i am having the following data in A1:B11 COL"A" COL"B" aabbccc 100 aabbcccaa 500 aabbxyz 50 aabbxyzab 200 aabbcccxy 300 aabbzzz 200 aabbzzzab 500 aabbxyzac 700 aabbyyyca 100 aabbcccda 800 aabbdddab 900 how can i get the sum of COL "B" wrt the substring of COL "A'? ie., the sum of substrings "ccc", "xyz", "zzz", "yyy", "ccc", & "ddd" of COL "A" , assuming that the substring always starts from the 5th character of COL "A"..!! hope i have detailed the things enough??!!! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 |
sum wrt substrings!
On Sat, 25 Mar 2006 12:56:33 -0600, via135
wrote: hi! i am having the following data in A1:B11 COL"A" COL"B" aabbccc 100 aabbcccaa 500 aabbxyz 50 aabbxyzab 200 aabbcccxy 300 aabbzzz 200 aabbzzzab 500 aabbxyzac 700 aabbyyyca 100 aabbcccda 800 aabbdddab 900 how can i get the sum of COL "B" wrt the substring of COL "A'? ie., the sum of substrings "ccc", "xyz", "zzz", "yyy", "ccc", & "ddd" of COL "A" , assuming that the substring always starts from the 5th character of COL "A"..!! hope i have detailed the things enough??!!! -via135 If you download and install Longre's free morefunc.xll add-in from you can use this formula: =SUMPRODUCT(ISNUMBER(FIND(MID(ColA,5,255),MCONCAT( SubStringsToSum,"|")))*ColB) SubStringsToSum is the named range where you enter the substrings you are looking for from Col A. So, it might look like: F1: ccc F2: xyz F3: zzz F4: yyy F5: ddd in the example you give. --ron |
sum wrt substrings!
hi Peo! all the three formulae give me the 0 value! -via135 Peo Sjoblom Wrote: Do you want the total for all these substrings? =SUM(SUMIF(A:A,"*"&{"ccc";"xyz";"zzz";"yyy";"ddd"} &"*",B:B)) for zzz only would look like =SUM(SUMIF(A:A,"*zzz*",B:B)) or even =SUM(SUMIF(A:A,"*"&D1&"*",B:B)) where you would put the substring in D1 -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "via135" wrote in message ... hi! i am having the following data in A1:B11 COL"A" COL"B" aabbccc 100 aabbcccaa 500 aabbxyz 50 aabbxyzab 200 aabbcccxy 300 aabbzzz 200 aabbzzzab 500 aabbxyzac 700 aabbyyyca 100 aabbcccda 800 aabbdddab 900 how can i get the sum of COL "B" wrt the substring of COL "A'? ie., the sum of substrings "ccc", "xyz", "zzz", "yyy", "ccc", & "ddd" of COL "A" , assuming that the substring always starts from the 5th character of COL "A"..!! hope i have detailed the things enough??!!! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 |
sum wrt substrings!
Either I misunderstood but using your example and copying an pasting into a
worksheet =SUM(SUMIF(A:A,"*"&{"ccc";"xyz";"zzz";"yyy";"ddd"} &"*",B:B)) returned 4350 which would be expected since all the strings contain substrings of those strings =SUM(SUMIF(A:A,"*zzz*",B:B)) returned 700 which seems to be correct as well I presume you have some other substrings than those in the example? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "via135" wrote in message ... hi Peo! all the three formulae give me the 0 value! -via135 Peo Sjoblom Wrote: Do you want the total for all these substrings? =SUM(SUMIF(A:A,"*"&{"ccc";"xyz";"zzz";"yyy";"ddd"} &"*",B:B)) for zzz only would look like =SUM(SUMIF(A:A,"*zzz*",B:B)) or even =SUM(SUMIF(A:A,"*"&D1&"*",B:B)) where you would put the substring in D1 -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "via135" wrote in message ... hi! i am having the following data in A1:B11 COL"A" COL"B" aabbccc 100 aabbcccaa 500 aabbxyz 50 aabbxyzab 200 aabbcccxy 300 aabbzzz 200 aabbzzzab 500 aabbxyzac 700 aabbyyyca 100 aabbcccda 800 aabbdddab 900 how can i get the sum of COL "B" wrt the substring of COL "A'? ie., the sum of substrings "ccc", "xyz", "zzz", "yyy", "ccc", & "ddd" of COL "A" , assuming that the substring always starts from the 5th character of COL "A"..!! hope i have detailed the things enough??!!! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 |
sum wrt substrings!
On Sat, 25 Mar 2006 15:11:25 -0500, Ron Rosenfeld
wrote: On Sat, 25 Mar 2006 12:56:33 -0600, via135 wrote: hi! i am having the following data in A1:B11 COL"A" COL"B" aabbccc 100 aabbcccaa 500 aabbxyz 50 aabbxyzab 200 aabbcccxy 300 aabbzzz 200 aabbzzzab 500 aabbxyzac 700 aabbyyyca 100 aabbcccda 800 aabbdddab 900 how can i get the sum of COL "B" wrt the substring of COL "A'? ie., the sum of substrings "ccc", "xyz", "zzz", "yyy", "ccc", & "ddd" of COL "A" , assuming that the substring always starts from the 5th character of COL "A"..!! hope i have detailed the things enough??!!! -via135 If you download and install Longre's free morefunc.xll add-in from you can use this formula: =SUMPRODUCT(ISNUMBER(FIND(MID(ColA,5,255),MCONCAT (SubStringsToSum,"|")))*ColB) SubStringsToSum is the named range where you enter the substrings you are looking for from Col A. So, it might look like: F1: ccc F2: xyz F3: zzz F4: yyy F5: ddd in the example you give. --ron Sorry, I forgot the URL for Longre's add-in: http://xcell05.free.fr --ron |
sum wrt substrings!
sorry Peo! of course..! after your strong reply i've found out that the mistake is on my part..! but not as presumed by u.. ! the problem is that i have entered the formula in the COL "A" itself! hats off to ur skill & patience! have a great day..! regds! -via135 Peo Sjoblom Wrote: Either I misunderstood but using your example and copying an pasting into a worksheet =SUM(SUMIF(A:A,"*"&{"ccc";"xyz";"zzz";"yyy";"ddd"} &"*",B:B)) returned 4350 which would be expected since all the strings contain substrings of those strings =SUM(SUMIF(A:A,"*zzz*",B:B)) returned 700 which seems to be correct as well I presume you have some other substrings than those in the example? -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "via135" wrote in message ... hi Peo! all the three formulae give me the 0 value! -via135 Peo Sjoblom Wrote: Do you want the total for all these substrings? =SUM(SUMIF(A:A,"*"&{"ccc";"xyz";"zzz";"yyy";"ddd"} &"*",B:B)) for zzz only would look like =SUM(SUMIF(A:A,"*zzz*",B:B)) or even =SUM(SUMIF(A:A,"*"&D1&"*",B:B)) where you would put the substring in D1 -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "via135" wrote in message ... hi! i am having the following data in A1:B11 COL"A" COL"B" aabbccc 100 aabbcccaa 500 aabbxyz 50 aabbxyzab 200 aabbcccxy 300 aabbzzz 200 aabbzzzab 500 aabbxyzac 700 aabbyyyca 100 aabbcccda 800 aabbdddab 900 how can i get the sum of COL "B" wrt the substring of COL "A'? ie., the sum of substrings "ccc", "xyz", "zzz", "yyy", "ccc", & "ddd" of COL "A" , assuming that the substring always starts from the 5th character of COL "A"..!! hope i have detailed the things enough??!!! -via135 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 -- via135 ------------------------------------------------------------------------ via135's Profile: http://www.excelforum.com/member.php...o&userid=26725 View this thread: http://www.excelforum.com/showthread...hreadid=526289 |
| All times are GMT +1. The time now is 10:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com