Pivot Table to count instances IF a condition met

[pfox]

Is it possible to create a pivot table counting instances of cells only IF a condition is met?

For example, goal is to create a column chart counting instances of the letter codes in G if column R value is >2, otherwise do not count towards the total

I hope that’s clear, thank you!

[JohnSUN]

Welcome!
It seems for me that you are looking COUNTIFS() function

[Villeroy]

Check the “Filter” option in the pivot dialog. Then you get an extra button where you can define a filter.

[pfox]

I’m not sure how I could use a ‘count if’ function when R:R>2 ? As it is I must manually uncheck all variables less than 2

[pfox]

For example, I would like to be able to quickly change the R:R value, say from 2 to 4, without having to manually click all the boxes!

[Villeroy]

Now it’s getting a bit complicated. This is because you try to mimic a database on sheets, like 90% of today’s spreadsheet users.

1. Call menu:Data>Define… and define the entire source list of your pivot table as a named database range. If that cell range makes up the entire data area on the sheet, you may skip this step and use the sheet name.
2. Call menu:File>New>Database…
   2.1 [X] Connect to existing database
   2.2. Type: Spreadsheet
   2.3. Specify your spreadsheet document.
   2.4. [X] Yes, register the database document
   2.5. Save the database document.
   This is a fake database. Nothing has been converted nor copied. It treats certain spreadsheet areas as if they were tables of a database.
3. Create a new query in design view.
   3.1.Add the database range from step 1 as source table.
   3.2. Add (double-click) the columns you need.
   3.3. Below the “B:B” field add criterion > :Parameter
   3.4. Hit F5 to test the query. It prompts you for a parameter and returns the selected rows based on that parameter.
   3.5. Save the query under some name.
4. Save the database document and forget the database, but don’t delete it.
5. Back to the spreadsheet, create a pivot from a registered data source.
   5.1. Specify the data source (by default the name of the database document) and the query.

Every time when you refresh the pivot table, you will be prompted for the parameter.
Insert new rows in order to add new data to the source range (the database range from step 1) and save the spreadsheet. You may need to restart the office suite before the pivot table gets the new data. this problem would not occur if you were saving data in a true database.

[JohnSUN]

@pfox In case you are afraid of Base and distrustful of databases, you can collect data for a histogram directly in Calc.
Select the strat column along with the data and choose Data - More Filters - Standard Filter with Options Copy results to and No duplication to create a list of unique values from this column somewhere else in the sheet. Now, next to this new list, write a formula for conditionally calculating values.
See, here’s how it’s done for test data - CountIfs_demo.ods (54.6 KB)

[Villeroy]

Pivot rIght–click>Properties…
Click “Options”, “Add filter”, [OK]
Now you have an additional button above the pivot table.
When you click that button, you get an ordinary filter dialog with operators = < > etc.
This filter filters the source data. The filter in the pivot table filter the results.