HI Umetica,
Here's an expansion/explanation of Hiroto's formulas.
DATA table
This is a table of sample data. Columns A, B and C are functionally equivalent to columns A, B and E (or A, C and E) of your Expense Detail table
Column A:
Contains date for each entry. The dates are entered directly.
Column B:
The entries here are equivalent to the information in columns B (or C) of your Expense Detail table. Each identifies one of the expense types listed in row 1 of the REPORT table, or in your example, Table 1 on the Expense Table sheet.
For the example, the class names are assigned randomly among the five class names, c1 through c5, using the formula shown.
Column C:
The entries here are equivalent to the information in column D (Cost) of your Expense Detail table.
For the example, each expense is a random multiple of 10 generated by the formula shown.
The three columns above are used to create a filled table of data. You already have the data in your Expense Detail table, so you may safely ignore the formulas above. The one in column D, though, is required in a column added to your Expense Detail table.
Column D: Index y|m|c
This column contains an index of the data entries in columns A, B and C, labelling each entry with a string containing the year, month number, and expense category, separated by the pipe character ( | ). The result is read by SUMIF to determine if the cost on this row is to be included in the sum for the cell containing (this copy of) SUMIF.
DATA::D2: =YEAR(A2)&"|"&MONTH(A2)&"|"&B2
YEAR(A2) reads the date and time value in cell A2, and returns the four digit date : 2015
& is the concatenation operator. It means 'append what follows to the existing text string.'
"" are text markers. Together they mean 'everything between us is text'
&"|" appends a pipe character to the year: 2015|
&MONTH(A2) MONTH(A2) reads the date in A2 and returns the (number of the) month ( 8 ); & appends the result to the string: 2015|8
&"|"&B2 appends a second pipe character after the month number; B2 reads and returns the contents of cell B2; the second & appends this to complete the string: 2015|8|c4
The formula in D2 is filled down to the rest of column D, and creates an index value for each line.
REPORT table:
Entered values:
A1: The year for which the data is being summarized. The entry is a four digit number.
B1 to F1: class/category names for expenses. These must be exact matches with the class names used in column C of the DATA table.
H2 to H9: number of the first month to be summarized, then incremented by 1 for each row. Note that the series of numbers continues beyond 12.
All other values on this table are created by the five formulas below:
A2: =LOWER(MONTHNAME(MOD(H2-1,12)+1))
Converts the number on this row of column H to the equivalent month name.
MOD(n,d) returns the remainder when a number (n) is divided by and other number (d). The -1 and +1 adjust the number and the remainder to make the end result range from 1 to 12 instead of 1 to 11, then 0.
MONTHNAME() converts that number to the name of that month.
LOWER() converts all letters in the name to lower case. If you want the name to begin with a capital, replace LOWER() with PROPER()
Fill this formula down column A to get the names of the other months.
I2: =A$1+QUOTIENT(H2-1,12)&"|"&MOD(H2-1,12)+1
This constructs the y|m part of the y|m|c index string needed by SUMIF. For a summary running from January to December in a single calendar year, the formula could be very much simplified. In that case, the year part would always by the number in A1, the month part would be the value in H2, and the values in column H would never be greater than 12. Formula: =A$1&"|"&H2 The rest is needed to handle dates beyond the end of the calendar year shown in A1.
A$1 returns the number in A1. The $ here is a fixed reference operator that keeps the row 1 reference constant as the formula is filled down column I.
+QUOTIENT(H2-1,12) returns the whole number answer when the number one is subtracted from the number in H2 and the result is divided by 12. The result is added to the year number returned above.
&"|"& appends a pipe character, as described above. The second & appends the result below.
MOD(H2-1,12)+1 returns a month number in the range 1 to 12. See previous section (cell A2 formula) for details.
The end result in I2 is 2015|8
B2: =SUMIF(DATA::$D,$I2&"|"&B$1,DATA::$C)
This constructs a y|m|c index specific to cell B2, using the y|m part in this row of column I, another pipe character, and the class/category part in row 2 of this column, then uses that y|m|c index value to select the items in DATA:: column D to be added.
SUMIF(test-values, condition, sum-values)
test-values: the y|m|c index values in column D of DATA
condition: matches the y|m|c index value for this cell ($I2&"|"&B$1 — this row of column I (2015|8) &"|" ( | ) & row 1 of this column ( c1 ) — 2015|8|c1
sum-values: the values in DATA::$C, summed if they are on a row with a test value matching the condition.
This formula is filled right to F2, then the row of formulas B2 to F2 is filled down. The $ operators keep the references to columns D, C and I fixed to those columns, and the references to row 1 fixed to that row. The references to column B and to row 2 are relative references and keep the same position relative to 'this cell' as the formula is filled into other cells. 'This cell' means "the cell containing 'this copy' of the formula.
G2: =SUM(B2:F2)
Calculates total expenses for the month recorded in this row. Fill down column G
B10: =SUM(B)
Calculates the total expenses for this column (category). Fill right to F10.
Row 10 must be a Footer row.
Row 1 is a Header row.
Column 1 is a Header column.
Regards,
Barry
