1

I've got some data in a Google Drive spreadsheet that I want to run a pivot table on. There are 3 columns Element, Cost, Omit as per screenshot below

screenshot of spreadsheet columns

I want to summarise the cost by element, but exclude any lines that have the value "omit" in the column "omit"

I've started to setup the pivot table, to summarise the cost by element which works fine, but I can't work out how to exclude the "omit" items in the pivot table summary.

To help, I've made a shared Google spreadsheet here, please go to File > make a copy if you would like to use it. Link to google drive spreadsheet : https://docs.google.com/spreadsheets/d/1gvsg8shqWJnK_zRxQUFcWDwJxE1ym6lSE35fSAqpx8s/edit#gid=0

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

  • Thanks, do you know how i can add a second or third filter value eg. =QUERY(A2:C42,"select A,B where C <>'Omit' or 'Omit2' or 'Omit3'") Ive tried reading up on the google sheet documentation for the query formula but could not find a solution – sam Oct 31 at 8:46
  • In the QUERY function you would need to use AND instead of OR. So (if I understand your question correctly) your formula would be: =QUERY(A2:C42,"select A,B where C <>'Omit' and C<>'Omit2' and C<>'Omit3'") – marikamitsos Nov 7 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.