Formula to find multiple matches & then sum result from another column into another sheet!

[justmeok]

Hi All

I have spent so much time trying to figure this out on my own with a spectacular lack of success

I want to lookup a variety of codes and then sum the balances from another column if code matches are found. There are 12 codes that I want to combine into one cell on another sheet in the same workbook. Not every code will appear each month so the formula needs to allow for no match to be found but to still combine the balances from the codes that are there.

I don't think I've done a good job of describing what I need so I will upload a sample spreadsheet that hopefully will make it clearer! The data on the sheet named LY Jul Data is exported from another program and I don't want to have to make any changes to the format etc but just use it as it is.

I have played around with array formulas, sumproduct, sumif, isna and combinations of them all but still can't figure out what to do!

Note the formula must work in Excel 2003 please.

[Fotis1991]

Do you mean in B125 and copy down, this?

Please Login or Register  to view this content.

[justmeok]

Hi Fotis
I knew I wasn't explaining it very well!!! What I need is for the 12 codes I have highlighted in yellow on LY Jul Data to be looked for and if found then the sum of the figures in matching rows in col C should be totalled and appear in sheet 1 at B125.
Thanks for trying to help me , much appreciated

[justmeok]

Hmmm not sure if I made it that much clearer! The result at B125 should be the combined total of all 12 codes or as many of the 12 codes that appear.
This again!

[Fotis1991]

I hope this time to understoond well!

[justmeok]

Hi Fotis, no not quite there yet!

Sheet 1 should just have one figure in B125 and that should total $53,161.87 which is the combined total of the following codes (from the list of 12) found on LY July Data sheet
6-2231 $9,803.70
6-2232 $3,650.08
6-2233 $25,769.22
6-2234 $17,307.70
6-2235 $2,769.24
6-2273 $1,237.50
6-2599 ($7,375.57)
No other cell in Sheet 1 should have a figure as in the live example they will already be populated with figures.

Thanks!

[Fotis1991]

Simple enough!

Just replace the FALSE condition in my formula to TRUE.

Please Login or Register  to view this content.

[justmeok]

Ahh yet again my explanation skills are lacking but I do like your creative solution! This unfortunately will not work as what I neglected to tell you is that in the live data there are more columns than in the example I sent on the sheet LY Jul Data (as there are also more on the one I have called sheet 1). Each month I need to compare results for the same period last year so I need this formula to work on freshly pasted data out of the software each month. The same number of accounts may not have an entry each month but when any of the 12 account codes do I need the formula to identify if the code is there and then sum the number in col c into cell B125 on sheet 1. Please note that on LY Jul Data sheet the area I have highlighted in yellow does not exist on the real data I just entered it to show the codes that need to be combined. Also none of these 12 codes will appear on sheet 1 they will always need to be summed to appear at code 6-2440 which is the new consolidated code.
I hope this time I have given a clearer explanation
Thanks again for your help and your patience!!

[Fotis1991]

Glad that you say that my suggestion works ok for the example that you uploaded.

The fact that your real workbook is completely different than the this one, is something different. Which is the reason for uploading a different example of your original? I am not able to understand this.

So i believe that you have to upload a sample workbook that looks exactly as your original and i hope that someone will be anle to helps you.

Good luck.

[justmeok]

Hi Fotis,

My sincere apology for wasting your time! I guess I was trying to speed up the process by uploading the shorter version of the spreadsheet as there are many sheets and columns in the spreadsheet and of course I needed to remove any confidential information. In my ignorance I didn't think that this would affect the answer but I see now that I was wrong.

Many thanks for trying to help me, I do appreciate it!

[Fotis1991]

Even now you can do this. Make a new-exactly as your original-worksheet and upload it here.

Perhaps me or someone else will be able to help you.

We are here not for punish anyone that does a mistake but we try to help as much as we can.

[justmeok]

Thanks for your understanding. I will prepare a similar version of the spreadsheet and upload when it is done. This might take a day or two! I'll be back