64 votes

How to group data by month in Google Sheets

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 ...
7 votes

How to group data by month in Google Sheets

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 ...
  • 16.9k
6 votes

How to group data by month in Google Sheets

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 ...
  • 69
5 votes

How to group data by month in Google Sheets

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 votes

How can I group a pivot table into weeks or months?

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 ...
4 votes
Accepted

Dynamically reference data from multiple tabs and sort it

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 ...
  • 11.8k
3 votes

How can I group a pivot table into weeks or months?

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 ...
2 votes

How can I group a pivot table into weeks or months?

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 ...
2 votes

Syntax of field "Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is"

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&...
  • 153
2 votes
Accepted

Syntax of field "Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is"

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" & ...
2 votes

How to group data by month in Google Sheets

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 ...
2 votes
Accepted

Google Sheets Pivot Table take first string

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

custom formula in pivot table google sheets

You should change your custom formulas to Year formula: ='time tracking'!A2:A=config!B3 Month formula: ='time tracking'!B2:B=config!B2
  • 11.8k
1 vote

Converting columns into rows with different values

This solution is not limited to just 8 lines. The formula is very complex and I would not be able to explain it to you. Try the test file
  • 1,620
1 vote

Converting columns into rows with different values

A result similar to the expected can be obtained using the following formula: ={{"First Name","Last Name","Street Address","City","State","Zip&...
  • 405
1 vote

How do I create a pivot table group in Google Sheets when the ungrouped values produce too many columns? Right click on the columns is not available

There are two options: 1 - Create a query, then create a Pivot table based on the query 2 - Create a query that includes a pivot table Query then Pivot Query - Use this formula: =query({arrayformula(...
  • 4,868
1 vote
Accepted

Keeping manually entered data side-by-side with pivot table data — row misalignment issue

You have a row misalignment issue. Hand-entered data is static and will stay put as dynamical formula results or pivot tables are free to expand and contract. One solution would be to make columns 3 ...
  • 7,353
1 vote
Accepted

A Google Sheet with variables for products

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 ...
  • 4,868
1 vote

Referencing a calculated value in another calculated value

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

Google Sheets Pivot Table take first string

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
  • 11.8k
1 vote
Accepted

Count query based on multiple conditions

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 ...
1 vote
Accepted

Google sheets - "Anchor" formula to pivot table data which keeps moving

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 ...
  • 11.8k
1 vote
Accepted

Summarise data in a pivot table, but exclude certain data if it has a certain value in a certain column

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
  • 11.8k
1 vote
Accepted

Create table showing difference between values in two pivot tables

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 ...
  • 1,908
1 vote

Is there a way to create a calculated field using results of another calculated field?

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 ...
1 vote

How to count duplicate names as one value in Sheets?

=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, ...
  • 24.9k
1 vote
Accepted

How to calculate weighted average of averages in Google Sheets?

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) ...
  • 4,667
1 vote

Can you create a pivot table in the same sheet as the data its referencing?

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 ...
  • 24.9k
1 vote
Accepted

How can I format a pivot table with staggered elements to make narrower?

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 ...
  • 198
1 vote

How to group data by month in Google Sheets

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 ...
  • 11

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