# SUMIF - trim and concatenate 2 sets of 2 columns giving answer in another column

#### chuf

##### Well-known Member
I’ve searched the forum but can’t find the answer to this question… I’m sure its easy given some of the amazing stuff I’ve seen you lot do.

I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.

I hope i've explained myself..

I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…

E.g.

A = 2000 text
B = 1234 text

F = 2000 trailing spaces, text
G = 1234 trailing spaces, text

I = 10,000

I want 20001234 = 20001234 = 10,000 for the whole column.

I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.

Many thanks. chuf

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### DonkeyOte

##### MrExcel MVP
could you elborate the SUMIF part (ie column J) -- not quite sure I follow what you're trying to do...

forcing J to = I where A:B + F:G match is not difficult, eg.

=I1*((A3+B3)=(0+F3+G3))

but I don't think this does exactly what you're after... ?

#### chuf

##### Well-known Member
Morning, thanks for your reply... didn't know how many other people would be excelling at this time of day.

I'm trying to sumif the values in column I ( there may be more than one for each concatenated combination) and put that value next in the summary sheet ( which will be shown in the report columns A and B ) in column J.

The difficulty, i think as a drongo with excel, is searching through the data in each of the columns to do the concatenate and trim and then sumif -ing if they are the same.

I'm sorry this sounds so garbled.

#### DonkeyOte

##### MrExcel MVP
ok, I think I understand -- one more Q

Are you saying A:B is the same number of rows as F:G or are you saying that A:B is some sort of summary of the values in F:G ... ie A:B contains the various combinations that appear in F:G but they are listed only once whereas in F:G they are listed numerous times ?

Hope that makes sense...

#### chuf

##### Well-known Member
Sorry didn't think to mention no. of rows, repetitions ... i vowed to learn excel a few years ago and logged onto this site.. but events overtook me and i've realised the error of my ways... i'm hoping to come back into the fold.

A:B are not in the same no. of rows as F:G.

A:B will be in the summary report of the sumif's of what's contained in the external data, lots of lines, represented by F:G. Its all this concateness and trim that's messing me up.

A:B will contain the combination only once but it may be in F:G more than once.

Is this an impossible dream ?

#### DonkeyOte

##### MrExcel MVP
ok that makes the formula slightly easier...

to be entered in J1 assuming first reference in A:B is row 1
-- note you may to change reference to row 1/1000 if your first concatentation in A:B is not in row 1 or your last row of data in F:G is > 1000...

this formula should be copied down for all rows in J

=SUMPRODUCT(--(((0+\$F\$1:\$F\$1000)+(0+\$G\$1:\$G\$1000))=(\$A1+\$B1)),--(\$I\$1:\$I\$1000))

#### chuf

##### Well-known Member
I've posted the actual formula below which shows an Value error...

=SUMPRODUCT(--(((0+'[Communities summary data.xls]summary'!\$A\$1:'[Communities summary data.xls]summary'!\$A\$65000)+(0+'[Communities summary data.xls]summary'!\$G\$1:\$G\$65000))=(\$B6+\$C6)),--('[Communities summary data.xls]summary'!\$K\$1:\$K\$65000))

Does the lack of a trim function matter ?

#### DonkeyOte

##### MrExcel MVP
no, TRIM shoudl not matter... the 0+ basically forces the number stored as text to become numeric (as would 1* or --)

what are the values in B6 + C6

to confirm

first number for results stored in B, second number in C ?
source data -- stored in a different file, sheet "summary" where first number (as text) in Col A, second number (as text) in column G and totals in col K

does the formula work in other rows / for other combinations ?

also you shouldn't need:

0+'[Communities summary data.xls]summary'!\$A\$1:'[Communities summary data.xls]summary'!\$A\$65000

the below should suffice:

0+'[Communities summary data.xls]summary'!\$A\$1:\$A\$65000

#### chuf

##### Well-known Member
B6 and C6 are numbers stored as text, those references that i want the sum if's from the source data.

Column K in the source data may have more than one value for the combination of references.

I've copied the code down a few rows but with no luck.

I could, as a last resort, copy the worksheet and do the concatenating in an additional column... i use the sumif statement then and it works, but i was assuming ( hoping) that this concatenating could be done as part of the formula and make me look clever

i love excel, and have been amazed at the formulae i;ve read on here.. but boy is it irritating when you don't know how..

#### chuf

##### Well-known Member
sorry, just realised should have said some of the values are not numbers, they are a combination of numbers and letters...

If the formula is converting the text numbers to numbers would this matter ? on the combination i have tried it is just numbers, and doesn't work for me, but apologies should have noted this earlier.

i started trying to do this over a week ago

Replies
2
Views
94
Replies
5
Views
327
Replies
3
Views
697
Replies
4
Views
342
Replies
6
Views
788

1,191,592
Messages
5,987,524
Members
440,099
Latest member
wai2kit

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back