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

 

[chuf]

Last query of the day i hope; this was something i didn't think would be possible but looking at my data again, and having searched through the forum it just may be.

In the Spreadsheet: Communities summary data, worksheet summary in column H there is a two digit code if the row relates to sports costs.

This would be shown as eg 03, 41 etc and would have trailing spaces.

Would it be possible to show a sumif in column G of my Department summary spreadsheet based on the reference in column B of that spreadsheet.

This should be - if column B of Department summary = column A of summary worksheet then sum the values in column K but ONLY if they have this two digit indicator in column H of the summary worksheet ?

I have tried to explain as best i can.. Here's hoping. Many thanks

 

[DonkeyOte]

contuining BH's formulae...


=SUMPRODUCT(--(LEFT('[Communities summary data.xls]summary'!A$1:A$3600,LEN(B1))=B1&""),--(LEFT('[Communities summary data.xls]summary'!G$1:G$3600,LEN(C1))=C1&""),--(ISERROR(0+LEFT('[Communities summary data.xls]summary'!H$1:H$3600,2))=FALSE),
,'[Communities summary data.xls]summary'!K$1:K$3600)

 

[chuf]

That is awesome, works first time.

Thanks very much - you have no idea how much of a difference this will make.

best wishes, Chuf