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:
----------------------------------------------------------------
[TABLE="width: 606"]
<colgroup><col span="7"><col></colgroup><tbody>[TR]
[TD="colspan: 2"]COMPANY[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]FM: 149186824[/TD]
[TD][/TD]
[TD]VAT NUMBERS FOR TEST
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CREDIT[/TD]
[TD]DEBIT[/TD]
[TD]SUM[/TD]
[TD][/TD]
[TD="align: right"]149186824
[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]14.00.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]338,05[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]338,05[/TD]
[TD][/TD]
[TD="align: right"]102958444
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]20.00.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]93,71[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]93,71[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]20.00.0013[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]112,09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]112,09[/TD]
[TD][/TD]
[TD]SUM BY OFFSET TEST
[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]62.03.0123[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]47,02[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]47,02[/TD]
[TD][/TD]
[TD="align: right"]=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))
[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]62.04.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]1.008,80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.008,80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]63.04.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]290,22[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]290,22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]63.98.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]33,72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]33,72[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]64.00.1023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]285[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]64.08.0023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]55,29[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]55,29[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]64.98.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]74,95[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]74,95[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]64.98.0023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]1.460,04[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.460,04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]73.10.0013[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]160,04[/TD]
[TD="align: right"]-160,04[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]73.10.0023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.217,15[/TD]
[TD="align: right"]-1.217,15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]75.00.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.020,00[/TD]
[TD="align: right"]-1.020,00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]75.00.0023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.094,00[/TD]
[TD="align: right"]-1.094,00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]-----------[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]COMPANY[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]FM: 102958444[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CREDIT [/TD]
[TD]DEBIT[/TD]
[TD]SUM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]61.92.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]451,42[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]451,42[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]64.98.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]0,3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0,3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
--------------------------------------------------------------------------------

sheet2 starting from A1
---------------------------------------------------------------------------------
[TABLE="width: 942"]
<colgroup><col><col><col span="2"><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CATEGORY DESCRIPTIONS[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CATEGORY CODES[/TD]
[TD="align: right"]65535
[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]9592886[/TD]
[TD="align: right"]3342489[/TD]
[TD="align: right"]3969910[/TD]
[/TR]
[TR]
[TD]CATEGORIZATION OF EXPENSE CODES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sumif test for all[/TD]
[TD]=SUMIF($C:$C;H2;$D:$D)
[/TD]
[TD="align: right"]205,8
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD]CATEGORY CODE[/TD]
[TD]SUM BY CATEGORY ON ALL CLIENTS[/TD]
[TD]test sumproduct for all[/TD]
[TD][/TD]
[TD]VAT NUMBERS PER CLIENT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]=SUMIFS(Sheet1!F:F;Sheet1!B:B;A5)
[/TD]
[TD="align: right"]=SUMPRODUCT((Sheet1!$F:$F);--(A5=Sheet1!$B:$B))
[/TD]
[TD][/TD]
[TD]FM: 149186824[/TD]
[TD="align: right"]=SUMPRODUCT(--($C$1:$C$10000=H$2);(Sheet1!$F$1:$F$10000);-($A$1:$A$10000=Sheet1!$B$1:$B$10000))
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]FM: 102958444[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]more vat…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]more vat…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]more vat…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]more vat…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]93,71[/TD]
[TD][/TD]
[TD][/TD]
[TD]more vat…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]112,09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]255
[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]3342489[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]3342489[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]3342489[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESCRIPTION[/TD]
[TD="align: right"]3342489[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
------------------------------------------------------------------------------------------------------------------------------------------------
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.
------------------------------------------------------------------
[TABLE="width: 384"]
<tbody>[TR]
[TD="colspan: 2"]COMPANY[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]FM: 111111111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CREDIT[/TD]
[TD]DEBIT[/TD]
[TD]SUM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]14.00.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]338,05[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]338,05[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]20.00.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]93,71[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]93,71[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]20.00.0013[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]112,09[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]112,09[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]62.03.0123[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]47,02[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]47,02[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]62.04.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]1.008,80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.008,80[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]63.04.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]290,22[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]290,22[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]63.98.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]33,72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]33,72[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]64.00.1023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]285[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]64.08.0023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]55,29[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]55,29[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]64.98.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]74,95[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]74,95[/TD]
[/TR]
[TR]
[TD]----[/TD]
[TD]-----------[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DATA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]COMPANY[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]FM: 222222222[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CREDIT[/TD]
[TD]DEBIT[/TD]
[TD]SUM[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]20.00.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]64.98.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2000[/TD]
[/TR]
</tbody>[/TABLE]
-------------------------------------------------------------------------------
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 .
----------------------------------------------------------------------------------------------
[TABLE="width: 989"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CATEGORY DESCRIPTIONS[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[TD]DESCRIPTION[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CATEGORY CODES[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]3342489[/TD]
[TD="align: right"]3969910[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CATEGORIZATION OF EXPENSE CODES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sumif test for all clients[/TD]
[TD="align: right"]10431,76[/TD]
[TD="align: right"]159,11[/TD]
[TD="align: right"]1299,02[/TD]
[TD="align: right"]318,72[/TD]
[TD="align: right"]2130,24[/TD]
[/TR]
[TR]
[TD]EXPENSE CODE[/TD]
[TD]DESCRIPTION[/TD]
[TD]CATEGORY CODE[/TD]
[TD]SUM BY CATEGORY ON ALL CLIENTS[/TD]
[TD]test sumproduct for all[/TD]
[TD][/TD]
[TD]VAT NUMBERS PER CLIENT[/TD]
[TD]sum per category by client[/TD]
[TD]sum per category by client[/TD]
[TD]sum per category by client[/TD]
[TD]sum per category by client[/TD]
[TD]sum per category by client[/TD]
[/TR]
[TR]
[TD]14.00.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]338,05[/TD]
[TD="align: right"]338,05[/TD]
[TD][/TD]
[TD]FM: 149186824[/TD]
[TD="align: right"]431,76[/TD]
[TD="align: right"]159,11[/TD]
[TD="align: right"]1299,02[/TD]
[TD="align: right"]318,72[/TD]
[TD="align: right"]130,24[/TD]
[/TR]
[TR]
[TD]20.00.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]65535[/TD]
[TD="align: right"]10093,71[/TD]
[TD][/TD]
[TD][/TD]
[TD]FM: 102958444[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]20.00.0013[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]112,09[/TD]
[TD][/TD]
[TD][/TD]
[TD]more vat…[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62.03.0123[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]10213316[/TD]
[TD="align: right"]47,02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62.04.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]1008,8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]63.04.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]9944516[/TD]
[TD="align: right"]290,22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]63.98.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]3342489[/TD]
[TD="align: right"]33,72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]64.00.1023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]3342489[/TD]
[TD="align: right"]285[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]64.08.0023[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]3969910[/TD]
[TD="align: right"]55,29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]64.98.0000[/TD]
[TD]DESCRIPTION[/TD]
[TD="align: right"]3969910[/TD]
[TD="align: right"]2074,95[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
-----------------------------------------------------------------------------------
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.
---------------------------------------------
[TABLE="width: 210"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]VAT[/TD]
[TD]VAT: 111111111
[/TD]
[/TR]
[TR]
[TD]EXPENSES CODE[/TD]
[TD]SUM[/TD]
[/TR]
[TR]
[TD]14.00.0000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]20.00.0000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]20.00.0013[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]62.03.0123[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]VAT[/TD]
[TD]VAT: 222222222
[/TD]
[/TR]
[TR]
[TD]EXPENSES CODE[/TD]
[TD]SUM[/TD]
[/TR]
[TR]
[TD]20.00.0000[/TD]
[TD="align: right"]5000
[/TD]
[/TR]
[TR]
[TD]62.03.0123[/TD]
[TD="align: right"]2000[/TD]
[/TR]
</tbody>[/TABLE]
-------------------------------------
Sheet2 contains the categorization of expenses and the calculations per VAT
---------------------------------------
[TABLE="width: 538"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]CATEGORIZATION OF EXPENSE CODES[/TD]
[TD][/TD]
[TD][/TD]
[TD]CATEGORY CODES[/TD]
[TD="align: right"]65535
[/TD]
[TD="align: right"]10213316
[/TD]
[/TR]
[TR]
[TD]EXPENSE CODE[/TD]
[TD]CATEGORY CODE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14.00.0000[/TD]
[TD="align: right"]65535[/TD]
[TD] [/TD]
[TD]VAT NUMBERS PER CLIENT[/TD]
[TD]sum per category by client[/TD]
[TD]sum per category by client[/TD]
[/TR]
[TR]
[TD]20.00.0000[/TD]
[TD="align: right"]65535[/TD]
[TD] [/TD]
[TD]VAT: 149186824
[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]VAT: 102958444
[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]20.00.0013[/TD]
[TD="align: right"]10213316[/TD]
[TD][/TD]
[TD]more vat…[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62.03.0123[/TD]
[TD="align: right"]10213316[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
----------------------------------------------------
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:

[TABLE="width: 268"]
<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>[TR]
[TD="class: xl65, width: 173, bgcolor: white"]VAT[/TD]
[TD="class: xl65, width: 184, bgcolor: white"]VAT: 111111111[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]EXPENSES CODE[/TD]
[TD="class: xl65, width: 184, bgcolor: white"]SUM[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]14.00.0000[/TD]
[TD="class: xl66, width: 184, bgcolor: white"]1000[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]20.00.0000[/TD]
[TD="class: xl66, width: 184, bgcolor: white"]1000[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]20.00.0013[/TD]
[TD="class: xl66, width: 184, bgcolor: white"]500[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]62.03.0123[/TD]
[TD="class: xl66, width: 184, bgcolor: white"]500[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"] [/TD]
[TD="class: xl65, width: 184, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]VAT[/TD]
[TD="class: xl65, width: 184, bgcolor: white"]VAT: 222222222[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]EXPENSES CODE[/TD]
[TD="class: xl65, width: 184, bgcolor: white"]SUM[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]20.00.0000[/TD]
[TD="class: xl66, width: 184, bgcolor: white"]5000[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 173, bgcolor: white"]62.03.0123[/TD]
[TD="class: xl66, width: 184, bgcolor: white"]2000[/TD]
[/TR]
</TBODY>[/TABLE]


Rather...

[TABLE="width: 281"]
<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>[TR]
[TD="class: xl65, width: 98, bgcolor: white"]VAT[/TD]
[TD="class: xl65, width: 183, bgcolor: white"]EXPENSES CODE[/TD]
[TD="class: xl65, width: 93, bgcolor: white"]SUM[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 98, bgcolor: white"]111111111[/TD]
[TD="class: xl65, width: 183, bgcolor: white"]14.00.0000[/TD]
[TD="class: xl66, width: 93, bgcolor: white"]1000[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 98, bgcolor: white"]111111111[/TD]
[TD="class: xl65, width: 183, bgcolor: white"]20.00.0000[/TD]
[TD="class: xl66, width: 93, bgcolor: white"]1000[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 98, bgcolor: white"]111111111[/TD]
[TD="class: xl65, width: 183, bgcolor: white"]20.00.0013[/TD]
[TD="class: xl66, width: 93, bgcolor: white"]500[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 98, bgcolor: white"]111111111[/TD]
[TD="class: xl65, width: 183, bgcolor: white"]62.03.0123[/TD]
[TD="class: xl66, width: 93, bgcolor: white"]500[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 98, bgcolor: white"]222222222[/TD]
[TD="class: xl65, width: 183, bgcolor: white"]20.00.0000[/TD]
[TD="class: xl66, width: 93, bgcolor: white"]5000[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 98, bgcolor: white"]222222222[/TD]
[TD="class: xl65, width: 183, bgcolor: white"]62.03.0123[/TD]
[TD="class: xl66, width: 93, bgcolor: white"]2000[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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).