software

hi!

i am having the following data in A1:B11

COLquot;Aquot; COLquot;Bquot;
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 quot;Bquot; wrt the substring of COL quot;A'?

ie., the sum of substrings quot;cccquot;, quot;xyzquot;, quot;zzzquot;, quot;yyyquot;, quot;cccquot;, amp; quot;dddquot;
of COL quot;Aquot; , assuming that the substring always starts from the 5th
character of COL quot;Aquot;..!!

hope i have detailed the things enough??!!!

-via135--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=526289Do you want the total for all these substrings?

=SUM(SUMIF(A:A,quot;*quot;amp;{quot;cccquot;;quot;xyzquot;;quot;zzzquot;;quot;yyyquot;;quot;dddquot;} amp;quot;*quot;,B:B))

for zzz only would look like

=SUM(SUMIF(A:A,quot;*zzz*quot;,B:B))

or even

=SUM(SUMIF(A:A,quot;*quot;amp;D1amp;quot;*quot;,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

quot;via135quot; gt; wrote in
message ...
gt;
gt; hi!
gt;
gt; i am having the following data in A1:B11
gt;
gt; COLquot;Aquot; COLquot;Bquot;
gt; aabbccc 100
gt; aabbcccaa 500
gt; aabbxyz 50
gt; aabbxyzab 200
gt; aabbcccxy 300
gt; aabbzzz 200
gt; aabbzzzab 500
gt; aabbxyzac 700
gt; aabbyyyca 100
gt; aabbcccda 800
gt; aabbdddab 900
gt;
gt; how can i get the sum of COL quot;Bquot; wrt the substring of COL quot;A'?
gt;
gt; ie., the sum of substrings quot;cccquot;, quot;xyzquot;, quot;zzzquot;, quot;yyyquot;, quot;cccquot;, amp; quot;dddquot;
gt; of COL quot;Aquot; , assuming that the substring always starts from the 5th
gt; character of COL quot;Aquot;..!!
gt;
gt; hope i have detailed the things enough??!!!
gt;
gt; -via135
gt;
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=526289
gt;On Sat, 25 Mar 2006 12:56:33 -0600, via135
gt; wrote:

gt;
gt;hi!
gt;
gt;i am having the following data in A1:B11
gt;
gt;COLquot;Aquot; COLquot;Bquot;
gt;aabbccc 100
gt;aabbcccaa 500
gt;aabbxyz 50
gt;aabbxyzab 200
gt;aabbcccxy 300
gt;aabbzzz 200
gt;aabbzzzab 500
gt;aabbxyzac 700
gt;aabbyyyca 100
gt;aabbcccda 800
gt;aabbdddab 900
gt;
gt;how can i get the sum of COL quot;Bquot; wrt the substring of COL quot;A'?
gt;
gt;ie., the sum of substrings quot;cccquot;, quot;xyzquot;, quot;zzzquot;, quot;yyyquot;, quot;cccquot;, amp; quot;dddquot;
gt;of COL quot;Aquot; , assuming that the substring always starts from the 5th
gt;character of COL quot;Aquot;..!!
gt;
gt;hope i have detailed the things enough??!!!
gt;
gt;-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,quot;|quot;)))*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


hi Peo!

all the three formulae give me the 0 value!

-via135Peo Sjoblom Wrote:
gt; Do you want the total for all these substrings?
gt;
gt; =SUM(SUMIF(A:A,quot;*quot;amp;{quot;cccquot;;quot;xyzquot;;quot;zzzquot;;quot;yyyquot;;quot;dddquot;} amp;quot;*quot;,B:B))
gt;
gt; for zzz only would look like
gt;
gt; =SUM(SUMIF(A:A,quot;*zzz*quot;,B:B))
gt;
gt; or even
gt;
gt; =SUM(SUMIF(A:A,quot;*quot;amp;D1amp;quot;*quot;,B:B))
gt;
gt; where you would put the substring in D1
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Northwest Excel Solutions
gt;
gt; www.nwexcelsolutions.com
gt;
gt; (remove ^^ from email address)
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;via135quot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; hi!
gt; gt;
gt; gt; i am having the following data in A1:B11
gt; gt;
gt; gt; COLquot;Aquot; COLquot;Bquot;
gt; gt; aabbccc 100
gt; gt; aabbcccaa 500
gt; gt; aabbxyz 50
gt; gt; aabbxyzab 200
gt; gt; aabbcccxy 300
gt; gt; aabbzzz 200
gt; gt; aabbzzzab 500
gt; gt; aabbxyzac 700
gt; gt; aabbyyyca 100
gt; gt; aabbcccda 800
gt; gt; aabbdddab 900
gt; gt;
gt; gt; how can i get the sum of COL quot;Bquot; wrt the substring of COL quot;A'?
gt; gt;
gt; gt; ie., the sum of substrings quot;cccquot;, quot;xyzquot;, quot;zzzquot;, quot;yyyquot;, quot;cccquot;, amp;
gt; quot;dddquot;
gt; gt; of COL quot;Aquot; , assuming that the substring always starts from the 5th
gt; gt; character of COL quot;Aquot;..!!
gt; gt;
gt; gt; hope i have detailed the things enough??!!!
gt; gt;
gt; gt; -via135
gt; gt;
gt; gt;
gt; gt; --
gt; gt; via135
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; via135's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=26725
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=526289
gt; gt;--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=526289Either I misunderstood but using your example and copying an pasting into a
worksheet=SUM(SUMIF(A:A,quot;*quot;amp;{quot;cccquot;;quot;xyzquot;;quot;zzzquot;;quot;yyyquot;;quot;dddquot;} amp;quot;*quot;,B:B))

returned 4350 which would be expected since all the strings contain
substrings of those strings

=SUM(SUMIF(A:A,quot;*zzz*quot;,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

quot;via135quot; gt; wrote in
message ...
gt;
gt; hi Peo!
gt;
gt; all the three formulae give me the 0 value!
gt;
gt; -via135
gt;
gt;
gt; Peo Sjoblom Wrote:
gt;gt; Do you want the total for all these substrings?
gt;gt;
gt;gt; =SUM(SUMIF(A:A,quot;*quot;amp;{quot;cccquot;;quot;xyzquot;;quot;zzzquot;;quot;yyyquot;;quot;dddquot;} amp;quot;*quot;,B:B))
gt;gt;
gt;gt; for zzz only would look like
gt;gt;
gt;gt; =SUM(SUMIF(A:A,quot;*zzz*quot;,B:B))
gt;gt;
gt;gt; or even
gt;gt;
gt;gt; =SUM(SUMIF(A:A,quot;*quot;amp;D1amp;quot;*quot;,B:B))
gt;gt;
gt;gt; where you would put the substring in D1
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; Northwest Excel Solutions
gt;gt;
gt;gt; www.nwexcelsolutions.com
gt;gt;
gt;gt; (remove ^^ from email address)
gt;gt;
gt;gt; Portland, Oregon
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;via135quot; gt; wrote
gt;gt; in
gt;gt; message ...
gt;gt; gt;
gt;gt; gt; hi!
gt;gt; gt;
gt;gt; gt; i am having the following data in A1:B11
gt;gt; gt;
gt;gt; gt; COLquot;Aquot; COLquot;Bquot;
gt;gt; gt; aabbccc 100
gt;gt; gt; aabbcccaa 500
gt;gt; gt; aabbxyz 50
gt;gt; gt; aabbxyzab 200
gt;gt; gt; aabbcccxy 300
gt;gt; gt; aabbzzz 200
gt;gt; gt; aabbzzzab 500
gt;gt; gt; aabbxyzac 700
gt;gt; gt; aabbyyyca 100
gt;gt; gt; aabbcccda 800
gt;gt; gt; aabbdddab 900
gt;gt; gt;
gt;gt; gt; how can i get the sum of COL quot;Bquot; wrt the substring of COL quot;A'?
gt;gt; gt;
gt;gt; gt; ie., the sum of substrings quot;cccquot;, quot;xyzquot;, quot;zzzquot;, quot;yyyquot;, quot;cccquot;, amp;
gt;gt; quot;dddquot;
gt;gt; gt; of COL quot;Aquot; , assuming that the substring always starts from the 5th
gt;gt; gt; character of COL quot;Aquot;..!!
gt;gt; gt;
gt;gt; gt; hope i have detailed the things enough??!!!
gt;gt; gt;
gt;gt; gt; -via135
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; via135
gt;gt; gt;
gt;gt; ------------------------------------------------------------------------
gt;gt; gt; via135's Profile:
gt;gt; gt; www.excelforum.com/member.php...oamp;userid=26725
gt;gt; gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=526289
gt;gt; gt;
gt;
gt;
gt; --
gt; via135
gt; ------------------------------------------------------------------------
gt; via135's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26725
gt; View this thread: www.excelforum.com/showthread...hreadid=526289
gt;On Sat, 25 Mar 2006 15:11:25 -0500, Ron Rosenfeld gt;
wrote:

gt;On Sat, 25 Mar 2006 12:56:33 -0600, via135
gt; wrote:
gt;
gt;gt;
gt;gt;hi!
gt;gt;
gt;gt;i am having the following data in A1:B11
gt;gt;
gt;gt;COLquot;Aquot; COLquot;Bquot;
gt;gt;aabbccc 100
gt;gt;aabbcccaa 500
gt;gt;aabbxyz 50
gt;gt;aabbxyzab 200
gt;gt;aabbcccxy 300
gt;gt;aabbzzz 200
gt;gt;aabbzzzab 500
gt;gt;aabbxyzac 700
gt;gt;aabbyyyca 100
gt;gt;aabbcccda 800
gt;gt;aabbdddab 900
gt;gt;
gt;gt;how can i get the sum of COL quot;Bquot; wrt the substring of COL quot;A'?
gt;gt;
gt;gt;ie., the sum of substrings quot;cccquot;, quot;xyzquot;, quot;zzzquot;, quot;yyyquot;, quot;cccquot;, amp; quot;dddquot;
gt;gt;of COL quot;Aquot; , assuming that the substring always starts from the 5th
gt;gt;character of COL quot;Aquot;..!!
gt;gt;
gt;gt;hope i have detailed the things enough??!!!
gt;gt;
gt;gt;-via135
gt;
gt;If you download and install Longre's free morefunc.xll add-in from
gt;
gt;you can use this formula:
gt;
gt;=SUMPRODUCT(ISNUMBER(FIND(MID(ColA,5,255),MCONCAT (SubStringsToSum,quot;|quot;)))*ColB)
gt;
gt;SubStringsToSum is the named range where you enter the substrings you are
gt;looking for from Col A. So, it might look like:
gt;
gt;F1:ccc
gt;F2:xyz
gt;F3:zzz
gt;F4:yyy
gt;F5:ddd
gt;
gt;in the example you give.
gt;
gt;
gt;--ronSorry, I forgot the URL for Longre's add-in:

xcell05.free.fr--ron


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 quot;Aquot;
itself!
hats off to ur skill amp; patience!

have a great day..!

regds!

-via135
Peo Sjoblom Wrote:
gt; Either I misunderstood but using your example and copying an pasting
gt; into a
gt; worksheet
gt;
gt;
gt; =SUM(SUMIF(A:A,quot;*quot;amp;{quot;cccquot;;quot;xyzquot;;quot;zzzquot;;quot;yyyquot;;quot;dddquot;} amp;quot;*quot;,B:B))
gt;
gt; returned 4350 which would be expected since all the strings contain
gt; substrings of those strings
gt;
gt; =SUM(SUMIF(A:A,quot;*zzz*quot;,B:B))
gt;
gt; returned 700 which seems to be correct as well
gt;
gt; I presume you have some other substrings than those in the example?
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Northwest Excel Solutions
gt;
gt; www.nwexcelsolutions.com
gt;
gt; (remove ^^ from email address)
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;via135quot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; hi Peo!
gt; gt;
gt; gt; all the three formulae give me the 0 value!
gt; gt;
gt; gt; -via135
gt; gt;
gt; gt;
gt; gt; Peo Sjoblom Wrote:
gt; gt;gt; Do you want the total for all these substrings?
gt; gt;gt;
gt; gt;gt; =SUM(SUMIF(A:A,quot;*quot;amp;{quot;cccquot;;quot;xyzquot;;quot;zzzquot;;quot;yyyquot;;quot;dddquot;} amp;quot;*quot;,B:B))
gt; gt;gt;
gt; gt;gt; for zzz only would look like
gt; gt;gt;
gt; gt;gt; =SUM(SUMIF(A:A,quot;*zzz*quot;,B:B))
gt; gt;gt;
gt; gt;gt; or even
gt; gt;gt;
gt; gt;gt; =SUM(SUMIF(A:A,quot;*quot;amp;D1amp;quot;*quot;,B:B))
gt; gt;gt;
gt; gt;gt; where you would put the substring in D1
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt;
gt; gt;gt; Regards,
gt; gt;gt;
gt; gt;gt; Peo Sjoblom
gt; gt;gt;
gt; gt;gt; Northwest Excel Solutions
gt; gt;gt;
gt; gt;gt; www.nwexcelsolutions.com
gt; gt;gt;
gt; gt;gt; (remove ^^ from email address)
gt; gt;gt;
gt; gt;gt; Portland, Oregon
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;via135quot; gt;
gt; wrote
gt; gt;gt; in
gt; gt;gt; message ...
gt; gt;gt; gt;
gt; gt;gt; gt; hi!
gt; gt;gt; gt;
gt; gt;gt; gt; i am having the following data in A1:B11
gt; gt;gt; gt;
gt; gt;gt; gt; COLquot;Aquot; COLquot;Bquot;
gt; gt;gt; gt; aabbccc 100
gt; gt;gt; gt; aabbcccaa 500
gt; gt;gt; gt; aabbxyz 50
gt; gt;gt; gt; aabbxyzab 200
gt; gt;gt; gt; aabbcccxy 300
gt; gt;gt; gt; aabbzzz 200
gt; gt;gt; gt; aabbzzzab 500
gt; gt;gt; gt; aabbxyzac 700
gt; gt;gt; gt; aabbyyyca 100
gt; gt;gt; gt; aabbcccda 800
gt; gt;gt; gt; aabbdddab 900
gt; gt;gt; gt;
gt; gt;gt; gt; how can i get the sum of COL quot;Bquot; wrt the substring of COL quot;A'?
gt; gt;gt; gt;
gt; gt;gt; gt; ie., the sum of substrings quot;cccquot;, quot;xyzquot;, quot;zzzquot;, quot;yyyquot;, quot;cccquot;, amp;
gt; gt;gt; quot;dddquot;
gt; gt;gt; gt; of COL quot;Aquot; , assuming that the substring always starts from the
gt; 5th
gt; gt;gt; gt; character of COL quot;Aquot;..!!
gt; gt;gt; gt;
gt; gt;gt; gt; hope i have detailed the things enough??!!!
gt; gt;gt; gt;
gt; gt;gt; gt; -via135
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt; gt; --
gt; gt;gt; gt; via135
gt; gt;gt; gt;
gt; gt;gt;
gt; ------------------------------------------------------------------------
gt; gt;gt; gt; via135's Profile:
gt; gt;gt; gt; www.excelforum.com/member.php...oamp;userid=26725
gt; gt;gt; gt; View this thread:
gt; gt;gt; www.excelforum.com/showthread...hreadid=526289
gt; gt;gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; via135
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; via135's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=26725
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=526289
gt; gt;--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=526289