SUMIF & Split Strings

[JimmyT10]

Hello!

If the attached screen shot works:

Aim: In F5 add all values from column B that are linked to 'A' in column A.
In F6 add all values from column B that are linked to 'B' in column A.
In F7 add all values from column B that are linked to 'C' in column A....and so on.



My problem:

In column B I have used =RIGHT( A5, LEN( A5 ) - FIND( " ", A5 ) ) to separate the string from column A, so the SUMIF function returns an empty cell.


If I enter the text manually (without splitting the string) SUMIF works, is there away around this?

All help, greatly appreciated. Thank-you in advance.Screen Shot 2015-01-15 at 7.30.11 pm.png

[Special-K]

Try

=SUMPRODUCT((LEFT(A$5:A$16,1)=E5)*(B$5:B$16))

[Special-K]

Try

=SUMPRODUCT((LEFT(A$5:A$16,1)=E5)*(B$5:B$16))

NOTE: With the data you've supplied you can shorten your routine in column B to

=RIGHT( A5, LEN( A5 ) - 2 )

though I suspect this will not work with the actual data as the "A" in "A 2239" will probably change to something longer

[JimmyT10]

Thanks for the swift reply!

Unfortunately when pasted in F5 - returned #value - error in formula.

Unlike excel, I don't assume it's you who made the mistake, have I made a newbie error here somewhere?

[JimmyT10]

Indeed, 'A' is a stand in. Got it finding the first 'space' in the string.

[AlKey]

Maybe this will work for you better

in B5 and copy down

=IFERROR(IF(LEFT(A5,FIND(" ",A5)-1)=E5,--RIGHT(A5,LEN(A5)-FIND(" ",A5)),0),0)

then in F5 and copy down

=SUMIF($E$5:$E$16,E5,$B$5:$B$16)

The solution could've been different with different layout.

Row\Col	A	B	C	D	E	F
4	Custormer 1	Values				SUM
5	A 2239	2239			A	2878
6	B 560	560			B	907
7	C 711	711			C	1238
8	D 907	907			D	1814
9	E 560	560			E	1036
10		0
11	Customer 2	0
12	A 639	639			A
13	B 347	347			B
14	C 527	527			C
15	D 907	907			D
16	E 476	476			E

[JimmyT10]

Hi Al,

Thanks for your help here. A bit more clarification here if you wouldn't mind.

Screen Shot 2015-01-16 at 6.50.11 pm.png

I've got you solution working pretty well on my test sheet, as seen here.

But the letters 'A, B, C, D, E' are stand in for customer names, 'Smith&Son' for example. Most have a different number of letters in their name. Is there something in this =IFERROR(IF(LEFT(A5,FIND(" ",A5)-1)=E5,--RIGHT(A5,LEN(A5)-FIND(" ",A5)),0),0), that specifically tells excel to look for on letter that would be stopping it working in my main sheet?

I used split string =RIGHT( A5, LEN( A5 ) - FIND( " ", A5 ) ) to search for the space (which is constant) and return everything after it.


Hope you can help again.

[AlKey]

Dear Jimmy,
Unfortunately, I can't always guess what one need or want. As a requester, it is your responsibility to provide information as accurate as possible. Otherwise you and I, both wasted our time.

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[daddylonglegs]

Based on the data in your first post, try using this formula in B5 copied down to extract the number as a numeric value

=REPLACE(A5,1,FIND(" ",A5),"")+0

Then F5 can just be a SUMIF formula using a wildcard

=SUMIF(A:A,E5&" *",B:B)

This works for customer names of any length, assuming the names themselves don't contain spaces

[JimmyT10]

Apologies Al. New to this. Didn't mean to waste your time.

Thank-you daddylonglegs! This has worked.