Total per month of daily values

numbers

I would like to have a total per month in second table of the first table
I would like the table to be separated and I would like the process to be automated: typing the month and year in one cell and having the spreadsheet calculate the total for that month. (it means i can keep data in a tab and just import/update it when I need without caring about which month stops where).

In my data table I have 2 columns: one is a date, the other a value

September 2014  114
September 2014  89
September 2014  109
September 2014  85
September 2014  96
September 2014  93
September 2014  96
September 2014  118
October 2014    107
October 2014    98
October 2014    79
October 2014    67
October 2014    86
October 2014    141
October 2014    92

The formula I'm using is:

=SUMIF(Date,"="&A2,Count)

Date is the date column in the first table;
in A2 i have the month and year (as mentioned above)—but this only returns the first value found for the month…
I tried using MONTH(Date) but it is not working; would you have any suggestions?

EDIT
my dates in the raw data table are formatted as follow:

01/09/2014 00:00:00

they are indeed Date, not String, so the SUMIF appears to be failing on this. How could I convert those Dates into Strings so the comparison inside SUMIF works?

data and formula
date colummn, Date not string and formatting options

Best Answer

Using the SUM(IF) function:

Because Numbers does not yet support certain Array functions, for example extracting MONTH(A1:A23), by parsing the date into a third column (which can be hidden) the function will work. This column can be in any table, not just the raw data table as long as the references are valid.

Hidden column formula:

=MONTH(A1)&YEAR(A1)

Where A1 is the cell with date.

Copy down then hide column if desired. (Importing more data by adding rows should automatically bring the formula down but unhide the column to be sure.)

=SUMIF($'Month & Year',"="&MONTH($A1)&YEAR($A1),$Value)

From the documentation regarding SUMIF():

The SUMIF function returns the sum of a collection of numbers, including only numbers that satisfy a specified condition.

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

  • test-values: The collection containing the values to be tested. test-values can contain any value.
  • condition: An expression that can include comparison operators, constants, the ampersand concatenation operator, and references. The contents of the condition must be such that the result of comparing the condition to another value results in the boolean value of TRUE or FALSE.
  • sum-values: An optional collection containing the values to be summed. sum-values can contain number values, date/time values, or duration values. It should have the same dimensions as test-values.

Tables

Formula

Hide Column

Related Question