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:
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?
my dates in the raw data table are formatted as follow:
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?
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:
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.)
From the documentation regarding