Numbers: how do you count/sum figures for a certain date range?

Why go to the trouble of entering and debugging difficult-to-understand formulas when the Numbers interface allows you to accomplish common tasks like this with a few clicks and no formulas?

For example it took me about 2 minutes to do this.

Starting from this (a subset of your example):

Add a category in the Date column:

Giving this:

Then add a category on the Category column:

Giving this:

Now tell Numbers to give you Subtotals.

Giving you this:

When you want to see your table without Categories just turn them off:

The Categories functionality in Numbers is quick, powerful, and flexible. If you wanted to see all GAS by month, for example, you would just drag the second Category "rule" in the panel on the right up above the one based on date.

SG

Hi Nicholas,

SUMIF will include a value in a sum IF a single specified condition is TRUE on a row.

To specify more than one condition, you need to use SUMIFS.

To sum your Gas expenses for January, for example, you need three conditions to return TRUE:

The label in column B is "GAS"

The Date in column A is on or after January 1

The Date in column A is before February 1.

Here's one way of organizing your categories and sums on a summary table. To reduce typing time, I've set all of the expense amounts to 1.00, and to avoid crowding in the summary table, I've formatted the results to show no digits after the decimal. In your table, which wil have wider columns, format these cells as Number, with two places after the decimal.

Table 2::B2: SUMIFS(Table 1::$C,Table 1::$B,B$1,Table 1::$A,">="&$A2,Table 1::$A,"<"&EDATE($A2,1))

Descriptions of SUMIFS and EDATE are available in the function Browser. To open, click on any empty cell and type =

Regards,

Barry

Hi Nicholas,

COUNTIF will count the number of transactions fitting the single condition, but won't provide a total of the amounts involved.

Here's a revision of the tables—adding the amounts from your transactions table, and providing more detail regarding the formula than I had time to write earlier.

I've also used conditional highlighting onthe summary table to hide cells containing zero amounts, and added a Year To Date Footer row to give current sums for each category.

Notes: Column A on both tables contains full Date and Time values, not text. The values are formatted to show only the short version of the month name plus the day number for the first day of that month, SUMIFS uses these dates to determine if each row fulfils the second and third of the three conditions necessary to have that row included in a particular sum.

Column B of Table 1 and Row 1 of Summary contain the names of the categories of expenses recorded in column C of Table 1. SUMIFS uses these values to determine whether each row meets the first of the three conditions necessary to have that row included in a particular sum.

Summary::B2: SUMIFS(Table 1::$C,Table 1::$B,B$1,Table 1::$A,">="&$A2,Table 1::$A,"<"&EDATE($A2,1))

SYNTAX: SUMIF(sum-values,test-values,condition,test-values,condition,test-values,condition)

sum-values,                      a set of values from which the values to be summed are chosen

test-values,condition,    a set of values to be tested, snd the condition those values must meet

test-values,condition,    a second set of values to be tested, snd the condition those values must meet

test-values,condition    a third set of values to be tested, snd the condition those values must meet

SUMIFS must have at least one test-values,condition pair, and may have several. In this case there are three, described in my initial response.

A value in a row where the category is GAS, the date is on or after January 1, AND the date is before February 1 will be included in the sum in Summary::B2

Categories: The categories named in row 1 of Summary must be exact matches for those used in column B of Table 1, but are not case sensitive (Gas = GAS). Category names in Summary are wrapped into two rows by Numbers because of the narrow columns I used in the example. They were entered with no character that would cause a line break, and Numbers sees then as the same value it would see if they were on a single line in a wider column.

In Table 1 above, only the test-values in row 3 meet all three conditions, and only the value in that row of Table 1, column C is included in the SUM seen in Summary::B2.

EDATE(date,month-offset)

date,                the date part of a date and time value

month-offset   a number specifying the number of months separating the date to be returned from the (starting) date

In the SUMIFS formula, EDATE provides the date of the first day of the month following the one in 'this row' of column A (or Summary)

The rest of the condition in this test-value,condition pair compares the date in column A of Table 1 to the date returned by EDATE. If the transaction date is before the date returned by EDATE, the condition is met.

The SUMIFS formula gets the Date at the beginning of the period to be summed from 'this row' of column A of Summary, and the category that must be matched from Row 1 of 'this column'

Table names used in the formula (Table 1) must match the actual name of the table being referenced. If your transaction table is not named "Table 1" you'll need to change that name in the formula to match the name of your table.

TAKE another look at your formula and your tables after reading this. What differences do you see between your formula and mine?

Does your formula return an error triangle? Click once on the red triangle to show the message. Copy it and post it in your reply

Open the formula editor on a cell that is not working. Press command-A to select the whole formula. Paste it into your reply along with the address of the cell (column letter and row number) it came from.

The devil is usually in the details, and we can't see those unless you show them to us.

Regards,

Barry