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

[chuf]

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

Answer in column J

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

 

[DonkeyOte]

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]

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]

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]

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]

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]

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 ?

thanks for your brainpower.

 

[DonkeyOte]

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]

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]

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