How to use query to filter data based on certain conditions in google sheet

weizer Published at Dev

weizer

Hi everyone,

I have 3 conditions from column B to column D. T= to the condition fulfilled in that particular date and F otherwise. All the 3 conditions have their own T and F in each date.

What I want to achieve is:

When I select On for condition 1, Off for condition 2 and On for condition 3, the QUERY function able to ignore the Condition 2 and give me the rows of the date where Condition 1 = T and Condition 3 = T. So basically if the Condition 2 is Off, then the query function will just looking at Condition 1 and 3 and extract the date where Condition 1 and Condition 3 is equal to T.

The data in column F is the expected output based on the On Off condition in B2:B4. May I know is there any way that I can achieve this automation? Any help will be greatly appreciated!

This is my sheet:

https://docs.google.com/spreadsheets/d/1zOB2rrysOQ7V6_Pa-caQN94Y-gFI_JT2gJ9Hf6tZQi8/edit#gid=0

Mike Steelson

Try, in C2 =if(B2="On","B='T'",) then C3 =if(B3="On","C='T'",) then C4 =if(B4="On","D='T'",) then in C5

=textjoin(" and ",true,C2:C4)

then

=QUERY(A8:D16,"Select A " & if(C5="",,"where "&C5&" "),1)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-09-12

Comments

0 comments

How to Copy data from One Google Sheet to another Sheet in the Same file Using Sheet Name as Criteria and find the sheet base on filter criteria

TOP Ranking

Article

How to use query to filter data based on certain conditions in google sheet

How to use query to filter data based on certain conditions in google sheet

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

pump.io port in URL

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Unable to use switch toggle for dark mode in material-ui

grouping by column variables and appending a new variable based on condition

BigQuery - concatenate ignoring NULL

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

Python Read Directory And Output to CSV

Remove adjacent duplicates in linked list in C

Angular 8. Unknown amount of http.get requests in array to call, must be sequential, what to use

How to keep curl session alive between two php processes?

Limit number of characters in uitextview

JMeter: Why get error when try to save test plan

Always setting the text cursor on Textbox

MTKView Displaying Wide Gamut P3 Colorspace

Vector input in shiny R and then use it

How to implement an authentication method using Spring Boot and JPA?

Laravel getting value from another table using eloquent

org.springframework.web.client.HttpClientErrorException: 401 null

When I click any button in my view page the form is submitted