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

2 Answers 2


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.

  • 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, 2019 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'") Nov 7, 2019 at 16:10

That can be done on pivot tables with a calculated field, remove the values in the pivot table and add a calculated field instead.

Select a custom sum type, and use the following formula:


Breaking down the formula it says: Sum 'Cost' if 'Omit' is different than "Omit" and 'Element' matches.

Here is the pivot table with the data you provided.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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