google sheets
I'm trying to have my spreadsheet to sum up values by month. Note that the dates are not organised chronologically. I've tried the solutions showing here but they are not working for me.
I'm not really good at this.
Here is my document: https://docs.google.com/spreadsheets/d/1rEqff3Ce_JRly4S2JJsRQP-QfQKBB9OVF9mYg-1ujl4/pubhtml
I came up with this solution.
(1) =TRANSPOSE(ARRAY_ROW(JAN!A1,FEB!A1,MAR!A1))
(2) =ARRAYFORMULA(VLOOKUP(A1:A3,VLOOKUP!A1:B3,2,0))
(3) =QUERY(A1:B3, "SELECT A, SUM(B) GROUP BY A LABEL SUM(B) 'Totals'")
(1)
(2)
(3)
The ARRAY_ROW formula allows for easy preparation of a range. The TRANSPOSE formula re-arranges the row into a column position. This way all entries are nicely presented (1). The VLOOKUP and the ARRAYFORMULA will efficiently look for all corresponding numbers in the VLOOKUP sheet (2). The QUERY formula is used to perform the totals calculation (3).
I've created an example file for you: Google Sheets advanced lookup + sum
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.
Best Answer
Please try putting the start and end dates (say in G1 and H1) and a formula such as:
Alternatively, since all your dates appear to be this year, you might extract the month number with something like:
copied down to suit and then create a pivot table with that number for Rows.