53

Google Sheets now supports this as 'Create pivot date group' In the Pivot table, once you've added your date/time column as rows: Right-click on one of the values in the Pivot table, Choose 'Create pivot date group' Choose the desired grouping (e.g., 'Month' or 'Year-Month') Before + Visual Example: After: More Info Google's documentation on the feature:...


21

Using the Data->Pivot table report... offers exactly what you are asking for. It might be possible that you have to use the "New Google Sheets". I use that as a default setting and it was easy to achieve what you wanted, similar as in Excel. Here in my answer I explain how to enable the new spreadsheets. It seems I did not completly understand the ...


7

Without a helper column: =query(A:E,"select sum(B), sum(C), sum(D), sum (E) group by month(A) offset 1") assuming day is in A1. Documentation that does a much better job of describing how the above works than ever I could.


6

I don't, except I suspect you are out of luck for the time being at least. However, even in Excel I have often found it as, or even more, useful to perform such grouping in the base data rather than in the pivot table. For me the summary provided by the table is often a prelude to drilling down for further detail and rather than end up then with many ...


5

An easier way that I found was to convert the date cell to text in a new column. =Text(A1, "mmm-yy") You can then pivot and it will group by month.


5

So for those who asked similar question as me, There is apparently no Group field function for Pivot in Google Sheet (as it there is in Excel) yet. Hence the simplest way is to add another column transforming your dates to the groups you need, e.g. weeks, months, years... Hopefully the functionality is soon in Google sheet too.


3

Use curly brackets within this very simple query: =QUERY({Alpha!A2:B;Beta!A2:B;Charlie!A2:B},"select * where Col1 <>'' order by Col2") The trick here is the curly brackets {....} we use to create and combine the arrays from the different tabs. Extra tip: {Alpha!A2:B;Beta!A2:B;Charlie!A2:B} A semicolon ; combines vertically (in columns, one ...


2

You can now do that in Google Sheets, similar to what you're describing for Excel: Add dates as rows/columns, right click on a value and select "Create pivot date group". This works for months (and year, etc), but unfortunately not for weeks. For that you still have to resort to something like pnuts is suggesting.


2

I was able to do what i needed with =and(date(2019, 7, 1) <= 'Expense Date', 'Expense Date' < date(2020, 7, 1)) in your case, i would expect that is =AND(<val> <> "Financial", <val> <> "Income") <val> will need single quotes if it has a space in it


2

Well, it's clunky, but this works: INDIRECT("R" & ROW() & "C" & COLUMN(),false) e.g. = AND(INDIRECT("R" & ROW() & "C" & COLUMN(),false) <> "Financial",INDIRECT("R" & ROW() & "C" & COLUMN(),false) <> "Income") I could make it a lot neater if I could pack some of this into a custom function, but I can't seem to ...


2

To get that output create pivot, add id1 and name1 as Rows, tick off totals (unnecessary) and add name1 to Values with COUNTA formula


1

Your question suggests that you are having two technical problems: First - you are confusing the difference between: Recording transaction data Presenting transaction information for analysis Second - products variants (color, size, design): how to take these into account when making sales. Transaction Data Each transaction should be recorded in a single ...


1

Since Calculated field needs defined range for Hours use: =(End - Start) * 24 For 3rd Calculated field - Count/Hour: = Count / ((End - Start) * 24)


1

Please try the following formula: =QUERY({B2:C;D2:E}, "select Col1,Col2, count(Col1) where Col1 is not null group by Col1, Col2 label count(Col1) ''") Functions used: QUERY


1

The following code will give you the exact output you are looking for in your bottom example: =arrayformula(if( query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '' group by Col1 PIVOT Col3",1),"select Col1,Col2,Col3 order by Col2 desc, Col3 desc")="",0, query(query({A:A,C:C*1,D:D},"select Col1,sum(Col2) where Col1 <> '...


1

You could use a QUERY formula combined with the SUM function: =sum(QUERY(A1:C11,"select sum(B), sum(C) where A<>'Grand Total'")) What you actually do is to recreate and sum the sums from the Pivot Table itself, omitting the values of the Grand Total. You can place the formula wherever you like.


1

To the right of column C create 2 new columns: D, F In cell D2 use this query: =QUERY(A2:C42,"select A,B where C <>'Omit'") Use these results for your pivot table. Link


1

I found a way I like better. I simply created a hidden column with the following steps: Round every day of the month to the first day of the month. =A2 - day(A2) + 1 where A2 is the standard date Set the formatting to custom and choose month / year. This will require you to delete the day setting. So, it will display month / year when the actual ...


1

Use IMPORTRANGE() to import data and VLOOKUP() to find match between Product Section. Here's a sample : First you need to import data from another sheet with IMPORTRANGE() You need to accept the access between sheets when you use IMPORTANGE: The best is to do this with a sample of every sheets to be sure you allow access for each one. To find match between ...


1

Accessing the values of one calculated field in another such field from the Pivot Table menu doesn't seem possible. Here are two facts that were encouraging, but I suspect are dead ends: Access columns from the origin sheet of your pivot table via their header values; There is a GETPIVOTDATA() function to access pivot table data. Regardless of trying to ...


1

=TRANSPOSE(QUERY(QUERY({A20:B}, "select Col1,Col2,count(Col1) group by Col1,Col2"), "select count(Col1) where Col2 = 'rock' label count(Col1)'Rock Count:'")) =COUNTA(UNIQUE(FILTER(A20:A, B20:B="rock")))


1

Kyle, it looks like you want this: cat 1: AVERAGE.WEIGHTED(B2:B6, C2:C6) cat 2: AVERAGE.WEIGHTED(B7:B11, C7:C11) cat 3: AVERAGE.WEIGHTED(B12:B17, C12:C17) cat 4: AVERAGE.WEIGHTED(B18:B22, C18:C22) If you don't always know how many listings there will be for each category: Set up a small table of two columns with headers, say "CAT | Weighted AVG" List ...


1

Simply select the cells of the pivot table you want to copy and then copy and paste it in the spreadsheet or sheet where you want it (or select desired cells and use CTRL + X and then CTRL + V to move it). If you want to, you can then delete the pivot table sheet.


1

The pivot table in Google Sheets is almost impossible to format, it drives me nuts! Often I use Query instead. I'm not sure if that will work for you your example, but take a look here on the example sheet here: https://productforums.google.com/forum/#!topic/docs/zLQMJAtsNLo;context-place=forum/docs


1

If you're already adding a new column, you can also just use eomonth("reference cell",0). That'll always grab the last day of the month. Or eomonth("reference cell",-1)+1 to get the beginning of the month. The beauty with this approach instead of converting to text is that when you pivot the data. It'll automatically sort ascending left to right.


1

None of the previous answers worked for me, because I use a form as input. You can't add formulas on the form-sheet. Here is my work-around. Reading the above, the Pivot table needs a column with month to select on. You can use a different criteria, like week or fridays. Or John, Doe, Foo or bar. Whatever you want to 'group'on. In my case: date is in B, =...


1

In Google Sheets, take a look at ISOWEEKNUM(). I created an extra column with the week number for each date, which I could then use in the pivot column under month for the same date to expand each month to week.


1

You can create a "pivot table group", from seconds to years, unfortunately, week is not available, for week grouping do as follows: format the dates as numbers. go to create dynamic group of "numbers" put 7 as the interval Optional: if you want a specific starting/ending date use numbers ex: 09/15/2020 = 44088 when the groups are formed,...


Only top voted, non community-wiki answers of a minimum length are eligible