We’re rewarding the question askers & reputations are being recalculated! Read more.

# Tag Info

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

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