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