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!
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!!!
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!
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.
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 ?
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
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
Check out this spreadsheet below. Specifically, look at the Month and Category tab. docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
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/
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
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.
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.
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.
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?
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.
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
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
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
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
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.
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.
@@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!!!
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! 😄👍
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.
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.
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?
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.)
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
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
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.
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
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.
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
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?
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.
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.
@@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.
Try this formula if there are multiple years of data. See the spreadsheet below. docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
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
@@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?
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......???
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!
That's AMAZING! Thanks so much for letting me know that you find such value in these videos!
This was exactly what i needed to make my data searchable by month thanks so much
That's so awesome to hear!! Thank you for letting me know. 😄
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!!!
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!
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.
Thanks so much for your feedback! I'm so glad you enjoyed the video, and also enjoy mouse selection style as well.
professional you make it easy, you are the api , thanks bro again
Thank you for your kind words! So glad you enjoyed the video!
Thanks a lot for making it easy. So glad found this video ❤
I'm so glad that you found it too! Super happy to help make things easier! 😄👍
This helped me a lot. Thank you.
Super happy this helped!
Great tutorials..👍
Thank you so much! 😊👍
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 ?
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
Wonderful video!
😊👍
Thank you ☺️
You're very welcome! 😊👍
hi! i would love to know if this will work with days as well?
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
hello, i want to ask, How to filter with two criteria (e.g: month and category)? thank you
Check out this spreadsheet below. Specifically, look at the Month and Category tab.
docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
Thanks for this effective example. Is there any way to use 01.01.2000 format rather than using 2-Jan-2023 style?
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/
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
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.
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.
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.
Does it work when dates are in rows?
Yes, it does. Success.
Glad you got it to work! 😊👍
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
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.
how to create filter for month and we want to know the total amount for that month?
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.
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?
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.
what can i do, if i just want to take source date Col H and J
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
@@spreadsheetlife thanks for the answer!! so cool
You're very welcome! 😄👍
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
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
can you tell about filter with 2 dropdown with month and year ?
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
@@spreadsheetlife Thanks for your answer, i have problem. what if 3 condition like 2 drop down (month and year) and other variable that created
So for each condition, just add another argument to the FILTER function. Hopefully this makes sense.
@@spreadsheetlife thanks, but what formula if i combine with sumif ?
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.
hello , this is for 12 month , now about next january 2024 ? how to count more
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.
Hello i;m from Indonesia. Thank you for sharing this tutorial. How about if i want to filter month from january to march?
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.
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?
Yes. Check this spreadsheet out. docs.google.com/spreadsheets/d/1dKPk8_9gwvcMgAhfbN4G3EOVFIg1yGhMaiVyBEMpK_I/copy
@@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!!!
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! 😄👍
@@spreadsheetlife Yes, your method was great.
2:36 it says #Value! , what should I do with it? The details don't appear. Thank you
How fascinating. Are you working with the sample file included with the video, or are you working with your own data?
I'm working on my own data
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.
Do you have tutorial how make this sheet from the start since it only shows when filtering the data by month thanks@@spreadsheetlife
Unfortunately, I do not currently have a tutorial that shows creating this setup from scratch. I'm sorry that's the case.
Can we do this for weeks and days as well?
Absolutely! If you'd like to know how, send me an email and I can show you.
Is there a way I can combine both the month and the year function together with the filter function?
Yes. Check out this spreadsheet.
docs.google.com/spreadsheets/d/1Xky_CIeZfCzhBxtJ63K5yJtNVQziO5O_2v63q_FaYF8/copy
H,@@spreadsheetlife Would also like to have Month and year filter. The spreadsheet above cannot be open. do you have another video for that?
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.
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?
Take a look at this. It may help.
docs.google.com/spreadsheets/d/1Xky_CIeZfCzhBxtJ63K5yJtNVQziO5O_2v63q_FaYF8/copy
@@spreadsheetlife Thankyou very very much,😇 i just added dropdown for year
Happy to help!! 😊👍
@@cmohan0004 Hi, how you did it? Would appreciate to get a copy of the spreadsheet.
Can source data taken from another sheet?
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.)
That define trick not worked😢
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.
Can you add the ability to filter using month AND date? I can't seem to get it to work. Thank you!
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
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
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.
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
everyone can help me
You are welcome to send me a link to your spreadsheet, and I will gladly take a look at it.
@@spreadsheetlife Can you make a video about this?
Hi there. Could you clarify your question for me? What are you asking me if I can create a video about?
what about if there is "ALL" in the dropdown list
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.
How can I remove all filters? I would like to view it without filters too.
Hi there! I'm a little confused about your question. What do you mean by removing the filters?
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
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?
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.
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.
@@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.
Okedokie. Send me an email at info@spreadsheetlife.com. It will be easier for me to help you with a more specific solution.
You have one year data. What if there are multiple years data?
Try this formula if there are multiple years of data. See the spreadsheet below.
docs.google.com/spreadsheets/d/1Onm9rSnTVBtbJ5kBLGVZLF92Fj_2QFbzpyHsZ8uU7XI/copy
Thank you very much
You're welcome 😊👍
hello thank you for this, what about when i want to filter by month and year? how will i do that?
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
@@spreadsheetlife thank you so much!!!
@@spreadsheetlifeThank you for your generous contribution. With your help, I have created a wonderful trading journal
That's amazing! Thank you so much for sharing. It really makes my day knowing my video helped you!
@@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?
Hi I followed all the step you said
But last I got message error.
You are welcome to email me, and I can help!
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......???
If you'd like, send me an email with a link to your spreadsheet, and I can take a look at it for you.
@@spreadsheetlife email address plz.....
@@spreadsheetlife email sent.....
MAIL SENT@@spreadsheetlife
I have replied to your email. 😊👍
😮
😄👍
Daily expense report
This could be applicable to a daily expense report for sure!
How to also add years? I want both filtered🥲
Saw your email! 👍