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.

 

[mbthemis]

i am trying to aplly the following formula in sheet2 with reference to sheet1
=SUM(OFFSET(INDIRECT(ADDRESS(MATCH(D9;Sheet1!$B:$B;0);2;;;"Sheet1!"));0;0;10;1))
but a get a #ref! error
if i brake it it works p.e. =ADDRESS(MATCH(D9;Sheet1!$B:$B;0);2;;1;"Sheet1!") gives me a result of 'Sheet1!'$B$1
the indirect works only in this format =INDIRECT("Sheet1!B1")
is there a way to get such a result in sum offset indirect address match formula for a different sheet???

 

[barry houdini]

Try using INDEX, e.g. like this

=SUM(OFFSET(INDEX(Sheet1!$B:$B;MATCH(D9;Sheet1!$B:$B;0));0;0;10;1))

 

[mbthemis]

Thank you very much Barry !!! that indeed worked, i busted my head with indirect address while index is so much flexier.

So i managed to find a formula that will calculate for every vat and every category the sum.
I ll try to describe for anyone interested.
in sheet2 in E1, is the category of the expenses code in cell D9, D10 etc are the vat numbers of each client.

So the formula is:
=SUMIFS(OFFSET(INDEX(Sheet1!$B:$B;MATCH($D9;Sheet1!$B:$B;0));0;0;MATCH($D10;Sheet1!$B:$B;0)-MATCH($D9;Sheet1!$B:$B;0);1);OFFSET(INDEX(Sheet1!$C:$C;MATCH($D9;Sheet1!$B:$B;0));0;0;MATCH($D10;Sheet1!$B:$B;0)-MATCH($D9;Sheet1!$B:$B;0);1);Sheet2!E$1)

analysis of the above:
the sum range is given by offset so as to calculate the cells only between the vat numbers that are in variable positions within the data that will be pasted in sheet1. So i match the VAT number of the 1st client (cell D9) in the sheet1 so as to start calculating from that point.
The number of rows that will calculate the sum is given by the (match - match)
(MATCH($D10;Sheet1!$B:$B;0)-MATCH($D9;Sheet1!$B:$B;0) ) at the rows part of the offset formula. In the first match i match the next clients vat row position (D10)- minus the client of calculation vat cell position (D9). (this is calculated as such because in the data sheet, after each vat start the expenses codes of each client, and they finish before the next vat)

In this part there is an unsolved problem with the last client that he has no next vat to calculate at the end. (any ideas are welcome here)

The next part of the sumifs calculation is to define the range of the criteria, which are the categories of the expenses codes that i had to index match next to the data sheet .
OFFSET(INDEX(Sheet1!$C:$C;MATCH($D9;Sheet1!$B:$B;0));0;0;MATCH($D10;Sheet1!$B:$B;0)-MATCH($D9;Sheet1!$B:$B;0);1) Again the range of the calculations is given by the formula (match - match) as above.

Here i have the main issue because so as to work all of the above, i will have to manually insert each time the index formula into the data sheet (sheet1), so as to assign to each expense code their category from sheet2, and that is not desirable. If you have any ideas on how to include (incorporate) this part to the calculation formula in sheet2 i will be so grateful !!
Is there a way to include this as criteria in my sumifs formula???