2

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


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

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


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


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

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


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