simple income table with date, monthly subtotals and a total

lunanova111 wrote:

I spent already hours trying to figure out how to get an extra row inserted after every month for the subtotals, sigh.

Hi Susanne,

Use another table for the subtotals.

Both tables have one Header Row and one Header Column.

Formula in Table 1 D2: MONTHNAME(MONTH($A2))

Fill down.

The function SUMIFS is your friend.

SUMIFS(sum-values, test-values, condition, test-values…, condition…)

In Subtotals, Column A is month name as Text. (That stops Numbers automatically inserting the year. If you want to sum per month over different years, we can do that).

Formula in B2: SUMIFS(Table 1::$C,Table 1::$D,$A2,Table 1::$B,B$1)

Fill down and fill right.

Please call back with questions.

Regards,

Ian.

Hi again Susanne,

A more complete solution:

lunanova111 wrote:

monthly subtotals which sum up to a total at the end.

Using the Charm of Numbers:

• Several tables, each with a purpose;
• Header Rows, Header Columns and Footer Rows that allow formulas to apply to all Body Rows or all Body Columns.

Formula in Footer Cell C22 of Table 1: SUM(C)

As you add more rows to Table 1, that formula will automatically include all Body Cells of Table 1 Column C.

In the Subtotals table, insert another Header Column (B)

Formula in B2: SUM(2:2)

Fill down.

That sums all Body Cells of that row.

It also allows for more columns (new accounts).

Formula in Footer Cell B14: SUM(B)

Just as a check on Table 1 Year Total!

Fill right to see Year totals for each account.

Regards,

Ian.