Hi Frances,
SUMIFS is your friend here.
For the example, which will contain only the columns necessary for the formulas, sheet 1 contains a single table, named "Ledger"
Column A lists the date of each item.
Column B lists the "outgoing" amounts.
Column C lists the "Incoming amounts.
The Table has one Header Row (row 1) and one Footer Row (the last row of the table)
The Header row contains labels for each column.
The Footer row contains the label "Totals" in column A, the formula =SUM(B) in column B, and the formula =SUM(C) in column C.
Sheet 2 contains a single table, named "Summary" (using a distinct name for each table removes the necessity of including the Sheet name in formulas referencing cells on the other table.)
Like Ledger, Summary has one Header Row and one Footer row.
Cell A1: Enter the year that the table is summarising.
Cells B1 and C1 contain the same labels as B1 and C1 on Ledger.
Other formulas:
Summary::A2: DATE(A$1,ROW()−1,1)
Syntax: DATE(year,month,day)
This constructs the Date part of a date and time string, in which the year is the number in A1, the month number is one less than the row number of the row containing the formula, and the day is 1. Numbers automatically completes the d&t value by appending the time, 00:00:00 (midnight, at the beginning of that day).
The cells are formatted as Date&Time, with the Date part displaying only the month name, and the Time part NOT displayed. (Calculation of the monthly sums uses (and requires) the actual date and time value in the cell, not just the text that is displayed.)
The formula is filled down column A to row 13 to calculate the date for the first of each month of the year entered in
Summary::B2: SUMIFS(Ledger::B,Ledger::$A,">="&$A2,Ledger::$A,"<"&EDATE($A2,1))
Syntax: SUMIFS(sum-values,test-values,condition,test-values,condition)
sum-values, The values in column B of Ledger, in rows where…
test-values,condition, The dates in column A of Ledger… are on or after the date in A2 of 'this table', AND
test-values,condition The dates in column A of Ledger… are before the date one month after the date in A2.
Both tests of a date must return 'true' for an amount to be included in that month's total.
Summary::C2: SUMIFS(Ledger::C,Ledger::$A,">="&$A2,Ledger::$A,"<"&EDATE($A2,1))
This is the formula in B2, filled right one column, into column C. The reference to column B (above) is automatically changed to column C. The absolute reference operator ( $ ) preceding each of the references to column A keeps that reference on the same column as the formula is filled right.
The three formulas are selected, then filled down to row 13 of Summary.
Chart/Graph:
Take a look at the x-y scatter chart in the Charting Basics template in Numbers. Post a further question if you get stuck.
Regards,
Barry
