sum wrt substrings!

V

via135

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
 
P

Peo Sjoblom

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
 
R

Richard Buttrey

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
Click to expand...

With your data in A1:B11, and a unique list of your substrings
starting in D1 (i.e. D1=ccc, D2=xyz etc), put the following in E1 and
copy down.

=SUMPRODUCT((MID(A1:A11,5,3)=D1)*(B1:B11))

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
R

Ron Rosenfeld

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
Click to expand...

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
 
P

Peo Sjoblom

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
 
R

Ron Rosenfeld

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
Click to expand...


Sorry, I forgot the URL for Longre's add-in:

http://xcell05.free.fr


--ron
 
H

Harlan Grove

Ron Rosenfeld wrote...
....
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
Click to expand...

Maybe using REGEX.FIND, but not using the built-in FIND.
 
V

via135

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
 
Top