SUMIFS using date function

That's unfortunate, since pivot tables were designed to streamline this exact kind of thing because they're a PITA to craft manually.

All is not lost, though, since your data isn't that complex.

The simplest solution would be to add another column to your data table, which calculates the YEAR() of the row's date. This column can be hidden afterwards if you don't want to see it.

The formula of cell B2 is simply:

=YEAR($A2)

Now you have a simple way to use SUMIFS() since you have common values (year and category):

In this table, cell B2's formula is:

=SUMIFS(Data::$D,Data::$C,"="&$A2,Data::$B,"="&B$1)

This sums the values in column $D on the data table based on matching column $C with cell $A2 (the category) along with column $B in the Data table with cell B$1

This formula can be filled across and down the table to get you your results.

This is literally a 4-click process.

Assuming your source data has columns labelled 'Category', 'Date' and 'Amount':

With your source table selected, choose Organize -> Create Pivot Table

In the Inspector you should see the various columns listed, along with how you want them summarized.

Drag the Category' field to the Rows box

Drag the Date field to the Columns box (it will summarize by year by default)

Drag the Amount field to the Values box (it will sum the values by default)

Done.

peterppan wrote:

I don't really understand why $Date,">="&"1/1/2020" doesn't give me an error whereas YEAR($Date),"="&C$1 does.

Numbers does not support keeping an 'array' of years calculated by YEAR($Date) in memory. So instead you have to first put the array of results in cells first (e.g., Camelot's extra column) and refer to that instead.

In contrast, $Date,">="1/1/2020" test-values, condition pair does not involve an array of calculated values.

SG