With practice from this video and from the Excel one, I feel like an expert in the FILTER function now! Thank you for your great explanations and great examples!
@spreadsheetlife10 ай бұрын
That's AMAZING! Thanks so much for letting me know that you find such value in these videos!
@barrybrideaux29195 ай бұрын
This was exactly what i needed to make my data searchable by month thanks so much
@spreadsheetlife5 ай бұрын
That's so awesome to hear!! Thank you for letting me know. 😄
@ryanlindsey6497 ай бұрын
Excellent video, thank you! Also love how you're using the mouse to select cells for the input, since that's often something I find confusing and frustrating when I'm trying to close a pop-up, and it enters the cell data for me.
@spreadsheetlife7 ай бұрын
Thanks so much for your feedback! I'm so glad you enjoyed the video, and also enjoy mouse selection style as well.
@user-qw4lz7yt4u11 ай бұрын
OMGG!!! THANK YOU FOR THIS ONE! I HOPE YOU MAKE MORE VIDS LIKE THIS :)) I have been searching for 3days now on how to do this :))) thank god i found your vid :) You made it so easy and straight to the point! thank you so much!!!
@spreadsheetlife11 ай бұрын
That warms my heart to hear! Thank you so much for your kind words! I'm so glad that you found this video, and that it provided what you were searching for!
@tal7atal7a6611 ай бұрын
professional you make it easy, you are the api , thanks bro again
@spreadsheetlife11 ай бұрын
Thank you for your kind words! So glad you enjoyed the video!
@rosaliaharjasumantri6 ай бұрын
Thanks a lot for making it easy. So glad found this video ❤
@spreadsheetlife6 ай бұрын
I'm so glad that you found it too! Super happy to help make things easier! 😄👍
@user-bb4pi3uo2d22 күн бұрын
Thanks so much for valuable information, i make my sheet last year 2023 but now in 2024 if choose march 2024 it filter data of march 2023 , how i can fix it ?
@spreadsheetlife22 күн бұрын
Check out this spreadsheet. It shows you how to filter by both Year and Month. I hope this helps. docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
@katherinewilwayco6 ай бұрын
This helped me a lot. Thank you.
@spreadsheetlife6 ай бұрын
Super happy this helped!
@SanisaI2 ай бұрын
Great tutorials..👍
@spreadsheetlife2 ай бұрын
Thank you so much! 😊👍
@mrsnkf104318 күн бұрын
hello, i want to ask, How to filter with two criteria (e.g: month and category)? thank you
@spreadsheetlife18 күн бұрын
Check out this spreadsheet below. Specifically, look at the Month and Category tab. docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
@UJUDigitalsАй бұрын
hi! i would love to know if this will work with days as well?
@spreadsheetlifeАй бұрын
Yes. Please see the spreadsheet below. The filter by date tab shows how to set up to filter by a single date. docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
@chasityj.41306 ай бұрын
Wonderful video!
@spreadsheetlife6 ай бұрын
😊👍
@kishoresivamaniraj14722 ай бұрын
hey i need a help i just want to know is it possible to filter the source data directly rather than creating another table please help me if anyone know
@spreadsheetlife2 ай бұрын
Yes, you can do this by selecting the data, and then go to Data > Create a Filter. You can then use the filter buttons to filter your data. I hope this helps.
@yungmlnd11 ай бұрын
Thank you ☺️
@spreadsheetlife11 ай бұрын
You're very welcome! 😊👍
@ReetardM87 ай бұрын
Thanks for this effective example. Is there any way to use 01.01.2000 format rather than using 2-Jan-2023 style?
@spreadsheetlife7 ай бұрын
Good question! Yes. You can change the formatting by changing the number formatting of the dates. And you can read more on number formatting here. spreadsheetlife.com/working-with-number-formats/
@momsheees5 ай бұрын
Hi, thank you for sharing. would like to know if there is a way to filter the year and the total amount as well? would appreciate your response.
@spreadsheetlife5 ай бұрын
Hey there! Yes, I saw your other comment and responded there. Try the link again and follow the instructions. If you are still having trouble after that, feel free to send me an email.
@KunalDey-j6k25 күн бұрын
My database format example is 22/04/24 And you show the example date format 22-jan-24 Please help me to make the sheet
@spreadsheetlife25 күн бұрын
This method should work the same way regardless of how the dates are displayed. 😊👍 Dates are always just stored as numbers in Google Sheets. It's only the number formatting that makes them appear differently.
@san702011 күн бұрын
how to create filter for month and we want to know the total amount for that month?
@spreadsheetlife11 күн бұрын
Based on your other comment, I think you found my other video to answer your question. If you still have questions though, let me know.
@hfrzflq11 ай бұрын
Hello there! Great explanation and simple example. But can you help me how to count how many data in each month as a quantitative number? For example, in April only 4 data input. In May only 6 data input. Thanks
@spreadsheetlife11 ай бұрын
Hi there! Are you asking how to count the number of records (rows) that are returned from the filter function? If so, here is your solution. Wrap the current formula inside of the COUNTA function, and divide that result by the number of columns from your result data. So the formula would like something like this (using example 1 from the video). =COUNTA(FILTER(G6:J20,MONTH(H6:H20)=E3))/4
@vernavong9 ай бұрын
hello , this is for 12 month , now about next january 2024 ? how to count more
@spreadsheetlife9 ай бұрын
Hi there! So this formula will filter by the month of January regardless of the year. So it would pull both 2023 and 2024 data if you have both.
@user-jw3uk6ji2u7 ай бұрын
How can I link the month filter to show for both sides at the same time? In other words, when I select the month of February the source data also filters the other months and only shows the month of February?
@spreadsheetlife7 ай бұрын
Hmm...interesting idea. To be honest, I do not think you can manipulate the setup I show in the video to accomplish what you are asking. However, let's say you want to filter a set of data without using the filter function. One thing you can do is select that data, then go to Data > Create a Filter. From there, you can filter by the date column. I hope this helps.
@yaqin85696 ай бұрын
Hello i;m from Indonesia. Thank you for sharing this tutorial. How about if i want to filter month from january to march?
@spreadsheetlife6 ай бұрын
Hi there! Thanks for watching. And that's a good question. I can help, but it will be easier for me to explain via email. So just send me an email and I'll get back to you.
@varuncd10964 ай бұрын
Does it work when dates are in rows?
@varuncd10964 ай бұрын
Yes, it does. Success.
@spreadsheetlife4 ай бұрын
Glad you got it to work! 😊👍
@woeisonglim3 ай бұрын
what can i do, if i just want to take source date Col H and J
@spreadsheetlife3 ай бұрын
What a creative question. I have an answer for you. For the first argument of the FILTER function, use the CHOOSECOLS function to select which columns you want. You have to use the CHOOSECOLS function since the two columns you want (H and J) are not next to each other. And the rest of the formula is the same. See the spreadsheet below for reference. docs.google.com/spreadsheets/d/1ps_BQDowyILf3J3NTmDR8y_WPpV9CKcIhE2NxzlcSSU/copy
@woeisonglim3 ай бұрын
@@spreadsheetlife thanks for the answer!! so cool
@spreadsheetlife3 ай бұрын
You're very welcome! 😄👍
@varuncd10964 ай бұрын
Can you suggest if I want to insert a date range with a start date and an end date when my dates are in a row? What will be the query?
@spreadsheetlife4 ай бұрын
Yes. Check this spreadsheet out. docs.google.com/spreadsheets/d/1dKPk8_9gwvcMgAhfbN4G3EOVFIg1yGhMaiVyBEMpK_I/copy
@varuncd10964 ай бұрын
@@spreadsheetlife I can't thank you enough. It does work. The problem was not the formula; the Google Sheet was not working on my device. After constant restart, it works. You won't believe how much it troubled me. Please make a short video about it. The formula is so simple. Everybody uses complex queries, I don't understand. Last, THANK YOU!!!
@spreadsheetlife4 ай бұрын
I'm sorry you had so much trouble, but I'm glad you got everything to work out in the end. I will add your suggestion to my list of potential videos. I'm so happy that this was a simple and useful solution for you! 😄👍
@varuncd10964 ай бұрын
@@spreadsheetlife Yes, your method was great.
@user-ux4cn9ev6h2 ай бұрын
can you tell about filter with 2 dropdown with month and year ?
@spreadsheetlife2 ай бұрын
I don't have a video that demonstrates this. But I do have this spreadsheet that you can take a look at that might help. docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
@user-ux4cn9ev6h2 ай бұрын
@@spreadsheetlife Thanks for your answer, i have problem. what if 3 condition like 2 drop down (month and year) and other variable that created
@spreadsheetlife2 ай бұрын
So for each condition, just add another argument to the FILTER function. Hopefully this makes sense.
@user-ux4cn9ev6h2 ай бұрын
@@spreadsheetlife thanks, but what formula if i combine with sumif ?
@spreadsheetlife2 ай бұрын
Combining your formula with SUMIF may complicate things a little. You are welcome to share your spreadsheet with me at ryan@spreadsheetlife.com, and I can take a look at it for you.
@CAGYAMAHA6 ай бұрын
Can we do this for weeks and days as well?
@spreadsheetlife6 ай бұрын
Absolutely! If you'd like to know how, send me an email and I can show you.
@christoph757711 ай бұрын
Can you add the ability to filter using month AND date? I can't seem to get it to work. Thank you!
@spreadsheetlife11 ай бұрын
Hi there! To filter by multiple conditions, separate those conditions with parentheses and an asterisk. FILTER ( range, (condition1) * (condition2) ) I hope this helps. Also, see this spreadsheet where I show how to write the condition to filter by date. docs.google.com/spreadsheets/d/1kvHGn5ROo_ueBggZ-kdeClBNhFZnB7Gu1F8B1nXOJBQ/copy
@JenniferKYoung7 ай бұрын
Thank you for the easy to follow tutorial!!!!! I'd like to know if there is anything to add to the formula to make it sort the dates by Day of month as well. the formula I have from this tutorial is =FILTER(J6:Q200,MONTH(P6:P200)=D3). I looked at the link you posted above, and was not able to find hwat I was looking for. Maybe I just use the drop down menu for the month, then sort range? wondered if I could skip that step. thanks in advance for your help! @@spreadsheetlife
@spreadsheetlife7 ай бұрын
Try nesting the FILTER function inside of the SORT function. Then you will have the ability to sort the results by date. Hopefully this answers your question.
@sagrindentertainment52698 ай бұрын
Is there a way I can combine both the month and the year function together with the filter function?
@spreadsheetlife8 ай бұрын
Yes. Check out this spreadsheet. docs.google.com/spreadsheets/d/1Xky_CIeZfCzhBxtJ63K5yJtNVQziO5O_2v63q_FaYF8/copy
@momsheees5 ай бұрын
H,@@spreadsheetlife Would also like to have Month and year filter. The spreadsheet above cannot be open. do you have another video for that?
@spreadsheetlife5 ай бұрын
Hi there! If you try to open the link above on a mobile device, sometimes it takes you to the original file instead of making a copy. If that's the case, go to File > Make a Copy, and then you can fully access the copy. I hope this helps.
@cmohan00046 ай бұрын
i am trying to make a payment ledger where i need not only month but year also, what modification can be done to filter it first with year and then month?
@spreadsheetlife6 ай бұрын
Take a look at this. It may help. docs.google.com/spreadsheets/d/1Xky_CIeZfCzhBxtJ63K5yJtNVQziO5O_2v63q_FaYF8/copy
@cmohan00046 ай бұрын
@@spreadsheetlife Thankyou very very much,😇 i just added dropdown for year
@spreadsheetlife6 ай бұрын
Happy to help!! 😊👍
@momsheees5 ай бұрын
@@cmohan0004 Hi, how you did it? Would appreciate to get a copy of the spreadsheet.
@Moon-bn8ez2 ай бұрын
2:36 it says #Value! , what should I do with it? The details don't appear. Thank you
@spreadsheetlife2 ай бұрын
How fascinating. Are you working with the sample file included with the video, or are you working with your own data?
@Moon-bn8ez2 ай бұрын
I'm working on my own data
@spreadsheetlife2 ай бұрын
Ok. My first suggestion would be to check to make sure that your dates are stored as date values and not text values. This could potentially be the issue. If that doesn't work, send me an email at info@spreadsheetlife.com. A screenshot or two may help me better diagnose the problem.
@Moon-bn8ez2 ай бұрын
Do you have tutorial how make this sheet from the start since it only shows when filtering the data by month thanks@@spreadsheetlife
@spreadsheetlife2 ай бұрын
Unfortunately, I do not currently have a tutorial that shows creating this setup from scratch. I'm sorry that's the case.
@kuli_ino6198 ай бұрын
thank you for your information. it's very helpfull. it work in me but i don't know why every i click october it not work and error. other mounth is work but october not
@kuli_ino6198 ай бұрын
everyone can help me
@spreadsheetlife8 ай бұрын
You are welcome to send me a link to your spreadsheet, and I will gladly take a look at it.
@ahnaf13514 ай бұрын
@@spreadsheetlife Can you make a video about this?
@spreadsheetlife4 ай бұрын
Hi there. Could you clarify your question for me? What are you asking me if I can create a video about?
@GuilhermeDemko6 ай бұрын
How can I remove all filters? I would like to view it without filters too.
@spreadsheetlife6 ай бұрын
Hi there! I'm a little confused about your question. What do you mean by removing the filters?
@GuilhermeDemko6 ай бұрын
Hi! Your video was very useful, everything went well. However, I would like to view all the data mixed together, without filtering. That I could see them all together too. I have two filters "Month" and "Category". =FILTER('Base de Dados'!A2:E151;(MONTH('Base de Dados'!A2:A151)=E23)*(TEXT('Base de Dados'!B2:B151;"MMMM")=D22)) @@spreadsheetlife
@spreadsheetlife6 ай бұрын
Oh I understand now! To show all results you could wrap the formula in an IF function. Test to see if the selections are blank using the ISBLANK function, and then return all results if that's true. Does that make sense?
@alfo-kl6ys2 ай бұрын
what about if there is "ALL" in the dropdown list
@spreadsheetlife2 ай бұрын
If you add an "ALL" to your dropdown list, then you can modify the formula (using example 1) to be this. =IF(C3="ALL",ARRAYFORMULA(G6:J20),FILTER(G6:J20,MONTH(H6:H20)=E3)) This will return all of the records if "ALL" is selected. I hope this helps.
@pakiatanonlinecoachingcenter3 ай бұрын
Can source data taken from another sheet?
@spreadsheetlife3 ай бұрын
Good question. Yes, source data can be taken from another sheet. Any time you reference data from another worksheet, you must write the reference like so: sheetName!A1:A10 (This example references the range A1:A10 from another worksheet.)
@pakiatanonlinecoachingcenter3 ай бұрын
That define trick not worked😢
@spreadsheetlife3 ай бұрын
I encourage you to send me an email with a screenshot or link to your file. I can then take a closer look at it for you.
@abhilashm67118 ай бұрын
Hi I followed all the step you said But last I got message error.
@spreadsheetlife8 ай бұрын
You are welcome to email me, and I can help!
@aabdullahhaasnain70333 ай бұрын
You have one year data. What if there are multiple years data?
@spreadsheetlife3 ай бұрын
Try this formula if there are multiple years of data. See the spreadsheet below. docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
@aabdullahhaasnain70333 ай бұрын
Thank you very much
@spreadsheetlife3 ай бұрын
You're welcome 😊👍
@BIRDSTORY-HINDI-URDU-ENGLISH6 ай бұрын
Hello dear......plz help me out with attendance sheet.....i made it for my self, but every attendance i put in January it repeated in all other months also.....what should i do......???
@spreadsheetlife6 ай бұрын
If you'd like, send me an email with a link to your spreadsheet, and I can take a look at it for you.
@BIRDSTORY-HINDI-URDU-ENGLISH6 ай бұрын
@@spreadsheetlife email address plz.....
@BIRDSTORY-HINDI-URDU-ENGLISH6 ай бұрын
@@spreadsheetlife email sent.....
@BIRDSTORY-HINDI-URDU-ENGLISH6 ай бұрын
MAIL SENT@@spreadsheetlife
@spreadsheetlife6 ай бұрын
I have replied to your email. 😊👍
@ricsib617310 ай бұрын
hello thank you for this, what about when i want to filter by month and year? how will i do that?
@spreadsheetlife10 ай бұрын
Check out the link to this spreadsheet. I put together a formula to filter by both Year and Month. Hope this helps. docs.google.com/spreadsheets/d/1Xky_CIeZfCzhBxtJ63K5yJtNVQziO5O_2v63q_FaYF8/copy
@ricsib617310 ай бұрын
@@spreadsheetlife thank you so much!!!
@geleg109 ай бұрын
@@spreadsheetlifeThank you for your generous contribution. With your help, I have created a wonderful trading journal
@spreadsheetlife9 ай бұрын
That's amazing! Thank you so much for sharing. It really makes my day knowing my video helped you!
@ricsib61733 ай бұрын
@@spreadsheetlife Hi it's me again, your tutorials are really helpful. I have another question please. Is it possible to filter by specific date range as well? for example, 15th April to 14th May?
@TheBearDenPhotography11 ай бұрын
Hi, a good tutorial. I've been looking for some time to sort my data by year, which is in a timestamp format. However, I want to do the sort within the existing source data and not create a seperate table/spreadsheet. I'm hoping I can modify your method to sort by year. My date format is a timestamp, mm/dd/yy hh:mm:ss such as "February 18, 2012 16:19:19". I'm going to play with what you have shown here to see if I can make it work. Any advise on how to do that would be greatly appreciated. Thanks.
@spreadsheetlife11 ай бұрын
So the goal is to sort your data by the timestamp field, and you are working in Google Sheets? Follow these steps. Select all the data including the headers.. Then go to Data > Sort Range > Advanced Range Sorting Options. Check the box next to "Data has header row." Sort by the Timestamp column. Click on Sort. Let me know if this helps to solve your problem.
@TheBearDenPhotography11 ай бұрын
@@spreadsheetlife Sort of. The goal is to sort my data by year only. I know how to do it the way you describe, I'm looking for a way to just select the timestamps by specific years using the method in your video.
@spreadsheetlife11 ай бұрын
Okedokie. Send me an email at info@spreadsheetlife.com. It will be easier for me to help you with a more specific solution.
@ThakurSahab-bf4qfАй бұрын
😮
@spreadsheetlifeАй бұрын
😄👍
@GautamSingh-fi2cg11 ай бұрын
Daily expense report
@spreadsheetlife11 ай бұрын
This could be applicable to a daily expense report for sure!