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:
----------------------------------------------------------------
<colgroup><col span="7"><col></colgroup><tbody>
</tbody>--------------------------------------------------------------------------------
sheet2 starting from A1
---------------------------------------------------------------------------------
<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.
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.