Combine 2 sumproducts from different sheets and SUM(OFFSET(INDIRECT(ADDRESS(MATCH(CONCATENATE))) combination

[mbthemis]

Hi to all, and thank you for all the great solutions that you provide in this forum.

I need to calculate the following in excel 2013 win7:
Data of the 1st sheet will be pasted from another software. In it there will be data of different customers. In B column there will be CODE of the expenses and in F column the SUM. CODE of expenses will be grouped into CODE CATEGORIES in sheet2. I need to calculate for every single customer by each code category the sum .
1-So i must generate a customers list (by its vat number) from sheet 1. (i can do it manually if it cant be done automatically)
2.-I must specify the range of cells that belong to each customer and contain their data. I have come up with this formula with which i can play : =SUM(OFFSET(INDIRECT(ADDRESS(MATCH(CONCATENATE("FM: ";M1);E:E;0);5));0;0;ABS(MATCH(CONCATENATE("FM: ";M2);E:E;0)-MATCH(CONCATENATE("FM: ";M1);E:E;0));2))
the vat is given by the software in "FM: 1015151515" format.

3. In sheet 2 i have grouped each expense code by category and each category has been marked by a number (i have done this by color and a macro command but since different excel versions show colors differently, in the example the categories are numbers).

4. I have tried with sumproduct and sumif as i show you in the table below but without success. I need to calculate somehow for each client (shorted by vat numbers), the sum of expenses in sheet1 column F, (with the following conditions) by each category at sheet 2 (H1 to M1) from the expense codes in the sheet1 that belong to this category (column B in sheet1 that matches the column A in sheet2. All this must include the offset function probably so as to understand the excel how many rows belong to each client.

These are the sheets -> with the formulas:
sheet 1 starting from A1:
----------------------------------------------------------------

COMPANY			FM: 149186824		VAT NUMBERS FOR TEST
			CREDIT	DEBIT	SUM		149186824
1	14.00.0000	DESCRIPTION	338,05	0	338,05		102958444
2	20.00.0000	DESCRIPTION	93,71	0	93,71
3	20.00.0013	DESCRIPTION	112,09	0	112,09		SUM BY OFFSET TEST
4	62.03.0123	DESCRIPTION	47,02	0	47,02		=SUM(OFFSET(INDIRECT(ADDRESS(MATCH(CONCATENATE("FM: ";H2);E:E;0);5));0;0;ABS(MATCH(CONCATENATE("FM: ";H3);E:E;0)-MATCH(CONCATENATE("FM: ";H2);E:E;0));2))
5	62.04.0000	DESCRIPTION	1.008,80	0	1.008,80
6	63.04.0000	DESCRIPTION	290,22	0	290,22
7	63.98.0000	DESCRIPTION	33,72	0	33,72
8	64.00.1023	DESCRIPTION	285	0	285
9	64.08.0023	DESCRIPTION	55,29	0	55,29
10	64.98.0000	DESCRIPTION	74,95	0	74,95
11	64.98.0023	DESCRIPTION	1.460,04	0	1.460,04
12	73.10.0013	DESCRIPTION	0	160,04	-160,04
13	73.10.0023	DESCRIPTION	0	1.217,15	-1.217,15
14	75.00.0000	DESCRIPTION	0	1.020,00	-1.020,00
15	75.00.0023	DESCRIPTION	0	1.094,00	-1.094,00
----	-----------
	DATA
COMPANY			FM: 102958444
			CREDIT	DEBIT	SUM
1	61.92.0000	DESCRIPTION	451,42	0	451,42
2	64.98.0000	DESCRIPTION	0,3	0	0,3

<colgroup><col span="7"><col></colgroup><tbody>
</tbody>

--------------------------------------------------------------------------------

sheet2 starting from A1
---------------------------------------------------------------------------------

					CATEGORY DESCRIPTIONS	DESCRIPTION	DESCRIPTION	DESCRIPTION	DESCRIPTION	DESCRIPTION	DESCRIPTION
					CATEGORY CODES	65535	10213316	9944516	9592886	3342489	3969910
CATEGORIZATION OF EXPENSE CODES					sumif test for all	=SUMIF($C:$C;H2;$D:$D)	205,8	0	0	0	0
DESCRIPTION	CATEGORY CODE	SUM BY CATEGORY ON ALL CLIENTS	test sumproduct for all		VAT NUMBERS PER CLIENT
DESCRIPTION	65535	=SUMIFS(Sheet1!F:F;Sheet1!B:B;A5)	=SUMPRODUCT((Sheet1!$F:$F);--(A5=Sheet1!$B:$B))		FM: 149186824	=SUMPRODUCT(--($C$1:$C$10000=H$2);(Sheet1!$F$1:$F$10000);-($A$1:$A$10000=Sheet1!$B$1:$B$10000))	0	0	0	0	0
DESCRIPTION	65535	0			FM: 102958444	0	0	0	0	0	0
DESCRIPTION	65535	0			more vat…
DESCRIPTION	65535	0			more vat…
DESCRIPTION	65535	0			more vat…
DESCRIPTION	65535	0			more vat…
DESCRIPTION	10213316	93,71			more vat…
DESCRIPTION	10213316	0
DESCRIPTION	10213316	112,09
DESCRIPTION	10213316	0
DESCRIPTION	10213316	0
DESCRIPTION	10213316	0
DESCRIPTION	9944516	0
DESCRIPTION	9944516	0
DESCRIPTION	9944516	0
DESCRIPTION	9944516	0
DESCRIPTION	9944516	0
DESCRIPTION	255	0
DESCRIPTION	3342489	0
DESCRIPTION	3342489	0
DESCRIPTION	3342489	0
DESCRIPTION	3342489	0

<colgroup><col><col><col span="2"><col><col><col span="6"></colgroup><tbody>
</tbody>

------------------------------------------------------------------------------------------------------------------------------------------------
I have put the formulas in sheet1 in cell H6 and in sheet2 in cells D5, E5, H5 and H3 please copy paste the above to see it in an excel.

 

[Aladin Akyurek]

Try to post smaller samples along with the expected results (without any formulas).

 

[mbthemis]

Ok ill try Aladin,

so in sheet1 we start from A1 . This sheet contains the pasted data from hundreds of clients. The rows for each client are variable and can be a few or many.
------------------------------------------------------------------

COMPANY			FM: 111111111
			CREDIT	DEBIT	SUM
1	14.00.0000	DESCRIPTION	338,05	0	338,05
2	20.00.0000	DESCRIPTION	93,71	0	93,71
3	20.00.0013	DESCRIPTION	112,09	0	112,09
4	62.03.0123	DESCRIPTION	47,02	0	47,02
5	62.04.0000	DESCRIPTION	1.008,80	0	1.008,80
6	63.04.0000	DESCRIPTION	290,22	0	290,22
7	63.98.0000	DESCRIPTION	33,72	0	33,72
8	64.00.1023	DESCRIPTION	285	0	285
9	64.08.0023	DESCRIPTION	55,29	0	55,29
10	64.98.0000	DESCRIPTION	74,95	0	74,95
----	-----------
	DATA
COMPANY			FM: 222222222
			CREDIT	DEBIT	SUM
1	20.00.0000	DESCRIPTION	10000	0	10000
2	64.98.0000	DESCRIPTION	2000	0	2000

<tbody>
</tbody>

-------------------------------------------------------------------------------
sheet2 contains the categorization of the expenses codes in columns A,B,C AND IN D column there is the sum for all clients by each expense code. In H6 and H7 and so on for each client i must calculate for each category the sum .
----------------------------------------------------------------------------------------------

						CATEGORY DESCRIPTIONS	DESCRIPTION	DESCRIPTION	DESCRIPTION	DESCRIPTION	DESCRIPTION
						CATEGORY CODES	65535	10213316	9944516	3342489	3969910
	CATEGORIZATION OF EXPENSE CODES					sumif test for all clients	10431,76	159,11	1299,02	318,72	2130,24
EXPENSE CODE	DESCRIPTION	CATEGORY CODE	SUM BY CATEGORY ON ALL CLIENTS	test sumproduct for all		VAT NUMBERS PER CLIENT	sum per category by client	sum per category by client	sum per category by client	sum per category by client	sum per category by client
14.00.0000	DESCRIPTION	65535	338,05	338,05		FM: 149186824	431,76	159,11	1299,02	318,72	130,24
20.00.0000	DESCRIPTION	65535	10093,71			FM: 102958444	10000	0	0	0	2000
20.00.0013	DESCRIPTION	10213316	112,09			more vat…
62.03.0123	DESCRIPTION	10213316	47,02
62.04.0000	DESCRIPTION	9944516	1008,8
63.04.0000	DESCRIPTION	9944516	290,22
63.98.0000	DESCRIPTION	3342489	33,72
64.00.1023	DESCRIPTION	3342489	285
64.08.0023	DESCRIPTION	3969910	55,29
64.98.0000	DESCRIPTION	3969910	2074,95

<tbody>
</tbody>

-----------------------------------------------------------------------------------
the results next to every vat is what i need to get by the new formula. Also the sumproduct really slows down my pc, but it might be inevitable if i need get this through.

 

[mbthemis]

Is this too big also, or the problem is generally too difficult?

Did i describe the problem in a comprehensive way?

 

[Aladin Akyurek]

Is this too big also, or the problem is generally too difficult?

Did i describe the problem in a comprehensive way?

Big indeed. Try to post smaller samples along with the expected/desired results, manually calculated using the samples.

 

[mbthemis]

ΟΚ another try.
Sheet 1 are the DATA. Each client is separated by VAT code.
---------------------------------------------

VAT	VAT: 111111111
EXPENSES CODE	SUM
14.00.0000	1000
20.00.0000	1000
20.00.0013	500
62.03.0123	500
VAT	VAT: 222222222
EXPENSES CODE	SUM
20.00.0000	5000
62.03.0123	2000

<colgroup><col><col></colgroup><tbody>
</tbody>

-------------------------------------
Sheet2 contains the categorization of expenses and the calculations per VAT
---------------------------------------

CATEGORIZATION OF EXPENSE CODES			CATEGORY CODES	65535	10213316
EXPENSE CODE	CATEGORY CODE
14.00.0000	65535		VAT NUMBERS PER CLIENT	sum per category by client	sum per category by client
20.00.0000	65535		VAT: 149186824	2000	1000
			VAT: 102958444	5000	2000
20.00.0013	10213316		more vat…
62.03.0123	10213316

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

----------------------------------------------------
so i need a formula that will sum the cells of each client in sheet1 separately, and then give me the sum by each expense category in sheet2. for example for the category 65535 the sum for the first client is 2000 (1000+1000) and for the second is 5000, while for the category 10213316 the sum for the 1st client is 1000(500+500) while for the second one is 2000.

I hope there is a solution.

 

[mbthemis]

Any ideas someone? Aladin is this comprehensible?

 

[Aladin Akyurek]

Any ideas someone? Aladin is this comprehensible?

I just copied the samples into Excel. Alas, I fail see the correlation between DATA and Sheet2... For example, the VAT: numbers of DATA do not occur on Sheet2. The field names do not provide any clue on the correlation one would expect.

As a side note but an important one from a processing point of view...

Records can better be headed with the VAT: number, thus repeated. Thus not:

VAT	VAT: 111111111
EXPENSES CODE	SUM
14.00.0000	1000
20.00.0000	1000
20.00.0013	500
62.03.0123	500
VAT	VAT: 222222222
EXPENSES CODE	SUM
20.00.0000	5000
62.03.0123	2000

<COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6144" width=173><COL style="WIDTH: 138pt; mso-width-source: userset; mso-width-alt: 6542" width=184><TBODY>
</TBODY>

Rather...

VAT	EXPENSES CODE	SUM
111111111	14.00.0000	1000
111111111	20.00.0000	1000
111111111	20.00.0013	500
111111111	62.03.0123	500
222222222	20.00.0000	5000
222222222	62.03.0123	2000

<COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3498" width=98><COL style="WIDTH: 137pt; mso-width-source: userset; mso-width-alt: 6513" width=183><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3299" width=93><TBODY>
</TBODY>

 

[mbthemis]

Aladin you are correct . when i tried to simplify the data i forgot to change the vat in sheet 2 . Unfortunatelly i will have no control in data sheet1. So the correlation that has to be made is to calculate for every vat the expenses code that belong to each category.For example for VAT 111111111 the sum of category 65535 will lookup in sheet1 only the 1000+1000 of the expense code 14.00.0000 and 20.00.0000 that belong to this category. When i try to sumif or sumproduct i cant do simultaneus calculation for all the expense codes of the category 65535. The calculation per expense code is easy, but i need it to be done automatically for all the codes that belong to each category per vat.
Thank you for giving the effort and sorry for the mistake.

 

[mbthemis]

i found a way to make the list of vat numbers from sheet1 to appear in sheet2
so in cell D9 of sheet2 the formula is
=INDEX(Sheet1!$B:$B;MATCH(0;COUNTIF(Sheet2!$D$8:$D8;Sheet1!$B$1:$B$11)+(LEFT(Sheet1!$B$1:$B$11;2)<>Sheet1!$H$4);0);1)

instead of ENTER i hit ctrl+shift+enter to make it an array.
This part of the formula (Sheet2!$D$8:$D8;Sheet1!$B$1:$B$11) gives unique distinct values but to work a previous cell than the one you put the formula to must be in it (p.e. D8 inside the formula and the cell that the formula sits to be D9).

The second part of count if works opposite than the formula (LEFT(Sheet1!$B$1:$B$11;2)<>Sheet1!$H$4) . It searches for the first 2 letters of cell H4 (it is the cell that contains your search criteria p.e. VAT), and then it lists the matching data (the fact that has <> instead of = somehow works in reverse).

when i scroll it down i get all vat listed in sheet1.

Now for the hard part of getting results per vat and category, i am still working hard on it. For now i find it easier if i use an index match formula next to the expense codes in sheet1 and get each ones category then the sumif in sheet2 is working (although i am not fully satisfied with it because it is not a single formula).