Use calculated fields in a Google Sheets pivot table to count rows where a field equals a specific value

Question

I have a sample sheet at https://docs.google.com/spreadsheets/d/1Of8JhSnFziFcxSqb4C4eRnZnob1ZuxAnqQOCGdzqvqI/copy.

I have a sheet/tab with data: Sheet1.

I have created a pivot table: Pivot Table 1.

In my pivot table I can count how many rows there are for each row groupingg.

What I am now trying to do is count how many rows are for a row grouping where a column value equals something.

Using my data as an example, I want my pivot table to have these columns:

• number of rows where Col 1 is equal to Type 1
• number of rows where Col 1 is equal to Type 1 and Col 2 is equal to Group 5

I tried to create a calculated field with the formula =IF('Col 1' = "Type 1", 1, 0) but it does not work.

I am trying to do this all in the pivot table, without using helper columns and what not. If I can't do it in this kind of pivot table, maybe a QUERY pivot?

Answer 1

Try

={unique(B:B),arrayformula(IFERROR(VLOOKUP(unique(B:B),query(A:E,"select B, count(C) where D='Type 1' and E='Group 5' group by B"),2,0),0))}