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

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

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 '19 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 '19 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:

``````=Sumifs(Data!B:B,Data!C:C,"<>Omit",Data!A:A,Element).
``````

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.