2

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

  • Do you want the sum the total for a Month or do you want to count how many times per month or both? Your Range setting is for the singular cell, and not for the range of all cells. – Ruskes Jun 18 '15 at 19:35
  • I would like to make the sum of count for any given month. The A2 contains the month I want to know the total for. Thanks :) – jrgd Jun 20 '15 at 19:33
2

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

  • hey @bjbk thanks for looking into this. I justified with your formula which is close to what i had. It's weird as it is still returning the same results, i.e.: the first 114 is returned and it won't sum the counts from B… – jrgd Jun 20 '15 at 19:20
  • just realising my date column is containing dates and are displayed as Month Year, but they remain Dates (i.e.: not string). How could i convert those Dates into Strings so the comparison inside SUMIF works? – jrgd Jun 20 '15 at 19:27
  • I updated my questions with the details about the Date content and the cells' formatting—thanks – jrgd Jun 20 '15 at 20:36
  • @jrgd now I see your problem. I'll update the answer with a fix for parsing the date shortly. – bjbk Jun 20 '15 at 21:11
  • great! i managed to get some results with MID, LEFT and VALUE but it's forcing me to create columns in my raw data : / Happy to read about a smarter and cleaner way ;) thanks for popping back and giving some thoughts on this – jrgd Jun 20 '15 at 21:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .