Google-sheets – Set default year to be something other than current year

To convert YYYY, MM, DD to an actual Date object, you need the DATE command. The following works:

=filter(A6:F371,A6:A371>=DATE(2015,1,1),A6:A371<=DATE(2015,1,31))

But I see you want to filter based on dropdown entry. For this I would use MONTH function. For example, suppose cell J10 has validation rule: list of items

01 (January), 02 (February), 03 (March), and so on

Then your filter in J11 could be

=filter(A6:F371,MONTH(A6:A371)=VALUE(LEFT(J10,2)))

The idea here is that the names of month help the user in selection, but the filter uses the numeric format (month number).

As I was building this question, I figured out several ways to achieve this, so I went ahead and shared the information.

There are several ways to do this. The first is a variation on your original syntax, but using nested IF statements instead of IF and AND:

=ARRAYFORMULA(SUM(IF(MONTH($A$1:$A$5)=MONTH(E1), IF(YEAR($A$1:$A$5)=YEAR(E1), $B$1:$B$5))))

The second uses the FILTER function. This method will return a #N/A error if FILTER doesn't find any matches for the conditions. FILTER takes each condition as a separate argument:

=SUM(FILTER($B$1:$B$5, MONTH($A$1:$A$5)=MONTH(E1), YEAR($A$1:$A$5)=YEAR(E1)))

The third uses INDEX and SUMPRODUCT:

=INDEX(SUMPRODUCT((MONTH($A$2:$A$6)=MONTH(E2))*(YEAR($A$2:$A$6)=YEAR(E2))*$B$2:$B$6), 1)

In each of these examples, I assumed that the data were in columns A and B, the "pivot table" dates were in column E, and the aggregated data are placed in column F.

There might be a way to do this with the QUERY function that provides an interface to the Google Visualization API Query Language, but I'm not sure. I don't know if such a query would dynamically update, either.