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