Sum yearly totals automatically

numbers

I am using a simple Numbers spreadsheet to track an expense that only comes up a few times per year. I want to be able to have Numbers automatically add the values that come from each year.

For example,

Column A    Column B    Column C    Column D
DATE          AMOUNT    2015            $500
1/15/2015       $500    2016           $1000
1/15/2016       $500
6/15/2016       $500

In other columns, I would like Numbers to sum all Amounts from 2015. Then below that, sum all Amounts for 2016, etc.

I have tried playing around with SUMIF and SUMIFS and cannot seem to get it right.

Best Answer

  1. Insert a column between column A and B (you can hide this column after).

  2. Fill the cells of this column with the following formulae:

    YEAR(A2)

    The column should be A for all cells, and the row number should be incrementing, such that the cell being referred to is always the one immediately to the left.

  3. In column D, fill the following formulae:

    SUMIF(Year,"=2015",Amount)

    Replace ‘Year’ with the column heading for the column you created in step 1.
    Replace ‘2015’ with the year that you are looking to be summed for that cell.

  4. Hide the column you created in step 1 (right-click on the column and choose Hide Column).

Related Question