Using FILTER function in google sheets

EngMarc

So read Valentine Schelstraete ‘How to use the FILTER function in Google Sheets’ article. I’ve also read other articles on using the FILTER function in excel.

I’ve tried variations of the FILTER function and cannot get it to work without an error. I’ve been through all the errors and solutions but still it doesn’t function.

I’m trying to figure out if it might be browser based functionality or if it is user error (likely)?

Here’s a screen shot of the google sheet and error. I’ve tried lots of variations with just a fixed filter region, unlimited rows, etc. Still get the same error. I initially had the data on another sheet so copied the data over and still got the same error.

Kind of lost since the error is very generic with multiple reasons resulting in the same error.

Karoline from Sheetgo

EngMarc Hi there!
You’re still getting this error because the range is incompatible with the rules of the formulas.

A column or row containing true or false values corresponding to the first column or row of range, or an array formula evaluating true or false.
condition2… - [optional] repeatable
Additional rows or columns containing boolean values TRUE or FALSE indicate whether the corresponding row or column in range should pass through FILTER. Can also contain array formula expressions that evaluate such rows or columns. All conditions must be of the same type (row or column). Mixing row conditions and column conditions is not permitted.

One way to do that is by filtering by two arguments,

I found a great video on google that shows in detail (besides being an excel example)

https://exceljet.net/functions/filter-function

EngMarc

Thank you, I didn’t pick up on the range must be the same type (column or row) as the criteria.

Let me try that and MUCH MUCH thanks for your input.

Karoline from Sheetgo

EngMarc
No worries! I’m happy to help and I hope you can sort it out!

Try and let me know if you get the expected results, if not, maybe we can think of another way to do this!

😉

EngMarc

Ok so got it to work finally across multiple sheets.

One thing I’m puzzled about is if the FILTER criteria doesn’t return any rows, how do I put ‘No Entries’ for instance in the output area?

EngMarc

I figured it out.

I’m using the ISNA() function with the IF(,,) function and it works perfectly.

Thank you again!

Karoline from Sheetgo

EngMarc This tip looks great, I’m gonna give it a shot too!

Anytime ;)