CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Solana_Station
Solana_Station

Reputation: 321

How to Pivot with multiple WHERE AND and OR conditions with Query function in Google Sheets

Goal: I want to create a pivot table with the QUERY function while using I have multiple criteria (using ANDs and ORs at the same time). The table basically consists of 2 columns: Col C is "Employee," Col B is "Status." The values for Status contains: "In Progress" "Consultation" "Require Information" and "Technical Issue." And there are multiple employee names that I won't bother listing. And I wanted it to basically count the number of statuses each employees have.

Now, I actually had a functional formula that was working well, however I wanted to add another criteria, which was to exclude (filter) one particular employee (let's say this person's name is "John A") from the result. And I want to be able to keep the criteria that I currently have and just add the new one. The formula before the modification is below.

Formula before modification: 

=QUERY('Blend Data'!$A$3:$C,"select C, COUNT(C) where (B = 'In Progress' OR B = 'Consultation' OR B = 'Require Information' OR B = 'Technical Issue') GROUP BY C pivot B",0)

Issue: However I when I made changes to the formula, the result didn't change and didn't get any errors.

Formula after modification:

=QUERY('Blend Data'!$A$3:$C,"select C, COUNT(C) where (B = 'In Progress' OR B = 'Consultation' OR B = 'Require Information' OR B = 'Technical Issue' AND C = 'John A') GROUP BY C pivot B",0)

Upvotes: 1

Views: 737

Answers (1)

player0
player0

Reputation: 1

try:

=QUERY('Blend Data'!A3:C,
 "select C,count(C) 
  where B matches 'In Progress|Consultation|Require Information|Technical Issue' 
    and C = 'John A' 
  group by C 
  pivot B", 0)

Upvotes: 1

Related Questions