Google Sheets FILTER - Advanced tricks with SUM, UNIQUE, IF, LEN, IFERROR
HTML-код
- Опубликовано: 30 июл 2024
- After watching this video, you will know how to use Google Sheets FILTER function in any way you want!🔥
📈 Try coupler.io for reliable and secure data import from various apps to Google Sheets. Become an early adopter: bit.ly/youtube-coupler
💌 Contact Railsware to build your software product bit.ly/contactrailsware
🤝 Join Railsware team bit.ly/railswarecareers
Learn the advanced tricks with UNIQUE, IFERROR, LEN, SUM, AVERAGE, COUNT, ARRAYFORMULA. Download your personal copy of a practice sheet and try them out: bit.ly/2GKLM46
Today you will learn:
🔹how to use OR + AND operators in FILTER formula
🔹how to apply Google Sheets Filter on multiple columns and order the output in any way you prefer
🔹how to combine FILTER with UNIQUE, SUM, AVERAGE, COUNT, IFERROR, LEN
🔹different ways to pull data without headers using the ROW function
why and how to use FILTER(FILTER(
🔹filter the correct data even if you change the order of columns in the original data source
🔹how to filter empty cells using ARRAYFORMULA + IF + LEN
Get your personal copy of Google Sheets practice file with advanced FILTER tasks here: bit.ly/2GKLM46
View additional materials from Railsware and join us on social media:
🔹Twitter: bit.ly/2QgEVnR
🔸RUclips: bit.ly/2CGr3wc
🔹Facebook: bit.ly/2IoaHs4
🔸Instagram: bit.ly/2Q9Mf00
🔹LinkedIn: bit.ly/2NLWfjj
🔸Blog: bit.ly/2zDshW
Super dense and helpful. I suggest watching this repeatedly on .75 speed for anyone less than professional. It would take a lot of work for the @Railsware crew but timestamps would be incredibly valuable to all advanced formula videos.
Wooow, you are Awesome!! Thanks for the content!!
great you've like it!
So helpful the content thanks!
thank you
este vídeo é muito bom, volto nele com frequência
Very smart content and approach. Thanks for taking the time!
Would like you to zoom it a bit more (+ center in the screen as well) 'cause my graphic card does not define the images well, sorry
Thank you! We use more zoom in our next videos. Subscribe to learn more:)
Very helpful and well paced tutorial.
Just want to add that some locales (like mine) preserve the comma (,) as number separator, in this case G sheet offers the backslash (\) as AND operator.
thank you! that's true. in one of our videos we've mentioned to pay attention to the locate, as we use the US one. But there can be a few problems in case you are using a different one.
0:50 uso de OU
1:44 Header
2:03 uso de AND
3:14 non adjacents columns
5:34 date
6:39 average and cont
7:48 filtering with dynamic reference to column names
9:49 iferror
10: 42 Len (NÚM.CARAC)
Thanks for the video, it's very helpful. I've encountered and issue where i want to assign different filter formula based on cell value; for instance : filter(A2:A, B2:A>100) & filter(C2:C, B2:B
I'm using the Filter function to select some data from a table, but whilst it returns the data just fine, I cannot get it to return the table headers.
I recall seeing some shortcut to do this (although it may have been on a different Dynamic Array function)
Please Can anyone enlighten me as to the correct syntax?
Hi, Thank you for the detailed videos. I am new to Googlesheets. It was really helpful. I wanted to make the formula font in the formula bar to permanently bold as in your vide. Can help me on how to do it?
What is you have multiple answers is a column Such as both EMEA and Africa in one cell. How would you filter that to show results for only EMEA in a different tab? The ="EMEA" would not include that one row as it had both. How would you have it included?
I am trying to build a sheet based on a list of symptoms provided and each cell can show more than one symptom. Need a way to tell the filter to include that row if it has that specific wording in my filtered column.
Eu achava que o Google Planilhas perdia para o Excel no uso da função filtro, e com este ótimo vídeo mudei de ideia. Este vídeo é ótimo.
Vocês não pensam em fazer um vídeo sobre os possíveis usos de {} nas funções?
Eu sempre encontro a sintaxe para o uso de {} de forma muito casual e discreta, embora este vídeo de vocês é o melhor ao reunir tanto conteúdo.
Perdão por me estender tanto mas estou com muitas dúvidas sobre o uso de {}.
Existe alguma fonte que poderiam indicar para eu me aprofundar no uso de {} nas funções do Google Planilhas?
Ou vocês pensam em fazer vídeos sobre {}?
Continuem com o ótimo trabalho
I can't find your video - "working with arrays in Google Sheets". You told about it at 3:05 minutes.
ruclips.net/video/DU6ZnIK92wM/видео.html
So on Google Sheets I have this:
=iferror(FILTER('RAW DATA 2'!$B$2:$G,'RAW DATA 2'!$B$2:$B>=$B$1,'RAW DATA 2'!$B$2:$B
Eu faço isso com frequência. (nota, digitei a sugestão conforme faço no Brasil, de um local para o outro há mudanças nos símbolos da sintaxe)
=iferror(FILTER(FILTER('RAW DATA 2'!$B$2:$G,'RAW DATA 2'!$B$2:$B>=$B$1,'RAW DATA 2'! $B$2:$B
Thanks. But what should I do, if there is a table
Title1 1 2 3 2
Title2 a b c d
Row3 9 8 7 6
Row4 5 4 3 2
And criteria is in another table that I must use Title1 and Title2 and look for Row3, as in find in column "Row3" and "2" in Title1 and find "b" in Titile2. Result should be "8"
please can anyone solve these two questions:
1- How to to state a header right in the filter function formula in excel 2021.
2- How to hide zero's that coming by the filter function formula.
Thank you very much
Awesome! Is there any way to bring the filtered content in a a-z classification using formulas?
Sure, try using SORT()
Thanks! Did not know about it
@@miguelribeiro1182 our pleasure to help!
I can't get past the formula shown at 1:21. No matter how I do it, I get an error message.
how to use unique and filter function together in wide range ie A1:K
First, filter out the data you need, then pull unique values from the filtered dataset - UNIQUE(FILTER())
How do you use indentation? Can't figure it out
Ctrl/Command+Enter for new lines and simple Space instead of Tab
Although you're sharing useful content, I feel like you're rushing, maybe you should slow down a bit and explain patiently. It's okay to have long videos!
Avinash S thank you for your feedback! You can actually modify the play speed in settings (use Settings in the bottom right corner of the video pannel). Hopefully this helps!
@@coupleracademy I tried tht option to slow down, but it's killing the interest to watch further! :"(
I agree, what's the hurry!? Good content but delivery was very bad.
ARRAYFORMULA( IF(LEN(A2:A)=0; ;GOOGLEFINANCE(A2:A);"price")))
Where is the mistake?
TWO FAST...
Try changing the playback speed in video settings to 1.25