Great trick! I use dynamic arrays extensively & have wanted to incorporate slicers for users’ ease of use, but couldn’t figure out how to do that. Now I know. Looking forward to adding this functionality. Thanks!
I guess Im randomly asking but does anyone know a method to get back into an instagram account..? I stupidly lost the password. I would appreciate any assistance you can give me.
@Elliot Clark Thanks for your reply. I found the site on google and Im waiting for the hacking stuff atm. I see it takes quite some time so I will get back to you later when my account password hopefully is recovered.
Yes, you’ve got it. This is just one application of using arrays to create Boolean logic. I think the reverse COUNTIF is a key part of achieving this in many contexts. 😀
Excellent techniques in this video Mark. Getting the chosen criteria info from multiple selections on multiple slicers is a problem I've never seen solved on video and you explain it very clearly. My particular use case is to add titles to pivot charts which requires an extra step of linking the slicers to additional pivot tables to drive the lists...I shall be using this technique very often. Very glad I've found your channel as so much useful content on here. Keep it up 👍
Cool use of slicers! It really makes it easy for people to combine regular formulas with slicers while keepings things relatively simple. Learned something about the TEXTJOIN function too. Are you planning on posting about Cube Formulas too?
I didn't understood what version of Excel do I need to have, for theses formulas to work in the way presented in the video. I mean Vlookup and Sumifs with multiple criteria in the first 5 minutes of the video. Can you please write this for me (the Excel version)? Great video by the way.
I'm using Excel 365, which automatically spills results into multiple cells. Check out this video: th-cam.com/video/TCwec8-tsDk/w-d-xo.html This video demonstrates the techniques that will work with Excel 365, and also earlier versions.
Thank you so much for the video, it is amazing! I just got stuck at one point, I have at my table a column with dates and in my formula I need to insert only dates from a specific period, but I can not make it work using countif. Is there any chance you could please help me out? I have tried countif(">="&Z10;$A$2:$A$20) and many other ways, but it did not work. Z10 is just a regular date in the same padron (short date).
Hi Mark, I have a additional requirement over and above the multiple slicers + multiple criteria solution you have given. Is there a way that I get the entire row or rows filtered out instead of getting the sum of the values.
For multiple selection in a single slicer i applied countifs and added sum but not getting the value when I select multiple data in slicer, please help
Hello Mark, i see you are using the slicer to filter by sales rep and then do a sumif. Is it possible to do a countif instead? My issue is that i’m trying to use the slicer to filter by month (data column is as dd/mm/yyyy) and then do a countif of values. Any insight would be very much appreciated.
In the video, I'm showing the principles to follow. It should work with COUNTIF. For you specific scenario, you may need to use the MONTH function to extract the month of the dd/mm/yyyy column.
When I try to insert any slices on any tables, a box comes up asking for "Existing Connections" asking to select a connection or this workbook's Data Model and there is nothing to select to proceed. Your thoughts please.
Ciao, the problem could be that you work with a very old version of office or with a Mac. Try to create a pivot table instead of a normal table and add a slicer this way.
Performance is a known issue when using a lot of array formulas, as the number of calculations performed by Excel in the background increases significantly. Given that most of this effect is formula driven, it comes down to your skills with picking and using the most efficient formulas for a given scenario.
Hello guys! I need a little help : when i do sumifs formula ( without the pivot with slicer criteria ) the formula it's work.But when i add the range and criteria for the pivot ( in the exemple for sales rep ) i have an #SPILL! error. Can you help me ?
The #SPILL! error most likely means - There are some cells blocking the spill range (check out this video for a better understanding of Dynamic Arrays) - You are trying to using the formula inside an Excel Table
Thank you so much! Just what I needed from min 15! Multiple criteria with multiple slicers!
Hopefully the first 15 minutes were not a waste. 😂
I think this is exactly what I needed! I will play with it and see if it works
Thank you 🙏
Check out this link too, which is an alternative approach.
th-cam.com/video/hOMgjI_Tlt8/w-d-xo.html
Fantastic presentation! I have an immediate use for this approach. Thanks.
Thank you. Hopefully you can put it to good use. 😀
Mark--Very slick presentation!
Thanks Alan 😀
You got another Subscriber. This is really brilliant. Also, nice pace. Thanks so much!!
Thank you 😀
Great Video...I am going to save it for my future references... 😄
Great stuff. It's a useful little trick from time to time.
Thanks super helpful video
thanks for your knowledge
simple file for easy to practice
thanks you very much
Thanks - I’m glad you could follow along and understand the principles.
Great trick! I use dynamic arrays extensively & have wanted to incorporate slicers for users’ ease of use, but couldn’t figure out how to do that. Now I know. Looking forward to adding this functionality. Thanks!
Thanks Jim - Dynamic arrays certainly make this technique easier to apply than traditional formulas 👍
I guess Im randomly asking but does anyone know a method to get back into an instagram account..?
I stupidly lost the password. I would appreciate any assistance you can give me.
@Francis Braden Instablaster =)
@Elliot Clark Thanks for your reply. I found the site on google and Im waiting for the hacking stuff atm.
I see it takes quite some time so I will get back to you later when my account password hopefully is recovered.
@Elliot Clark it worked and I actually got access to my account again. Im so happy:D
Thank you so much you really help me out !
Thank you Sir !
very useful itself and also a generator of ideas for other uses of Boolean logic
Yes, you’ve got it. This is just one application of using arrays to create Boolean logic. I think the reverse COUNTIF is a key part of achieving this in many contexts. 😀
This is brilliant! I'm hoping this will work with filter and xloopup.
Thank You. Yes, it should work with both of those functions. 👍
super helpful and creative use of formula logic. Thank you.
Thanks, I’m glad you found it useful.
That was pure genius..
Thanks… I appreciate that 😀
Thank you, your explanations are easy and to the point. Can't wait to try it with my projects👍👍
Great, pleased I could help 😊
Excellent techniques in this video Mark. Getting the chosen criteria info from multiple selections on multiple slicers is a problem I've never seen solved on video and you explain it very clearly. My particular use case is to add titles to pivot charts which requires an extra step of linking the slicers to additional pivot tables to drive the lists...I shall be using this technique very often. Very glad I've found your channel as so much useful content on here. Keep it up 👍
It’s a bit of a hack, but works for most people.
I now wonder if a slicer connected to a Table would actually be a better option.
@@ExcelOffTheGrid I tried it but the table just filters so you never know which row numbers your results will be on. Pivot tables are the way to go.
Waoo great work.....
Thanks a lot 😊
Absolutely Amazing & Helpful - This can be used in multiple places
Thanks - it can get a little complicated with multiple slicers and multiple selections. But hope you find it a useful technique. 😀
Cool use of slicers! It really makes it easy for people to combine regular formulas with slicers while keepings things relatively simple. Learned something about the TEXTJOIN function too. Are you planning on posting about Cube Formulas too?
Thanks Rick - Cube formulas have been in my content list for a long time. They are a pretty cool feature. Maybe later in the year.
This is great, thank you for taking the time to share your brilliance
My pleasure! :-)
It's fantastic bro
Thank you, I’m glad you liked it.
That was very good. Thank you.
Thanks Dale. I'm glad you enjoyed it :-)
Thanks for sharing❤
You're welcome Frank.
Brilliant!
Thanks Paula 😊
Subscribed. Thank you!
You are awesome, thank you!
Marvelous
Thanks :-)
Awesome! 🚀
Thanks. I've got a newer method which might interest you too.
th-cam.com/video/hOMgjI_Tlt8/w-d-xo.html
@@ExcelOffTheGrid thanks!
Too Good! Nice.
Thanks Rishab 😀
I knew how to use the slicers, but I wasn't familiar with the reverse COUNTIF technique. Thanks
I suspect I may have invented the term ‘Reverse COUNTIF’, but it seems like the best way to describe it.
Excellent
Thanks 😀
I didn't understood what version of Excel do I need to have, for theses formulas to work in the way presented in the video. I mean Vlookup and Sumifs with multiple criteria in the first 5 minutes of the video. Can you please write this for me (the Excel version)? Great video by the way.
I'm using Excel 365, which automatically spills results into multiple cells. Check out this video:
th-cam.com/video/TCwec8-tsDk/w-d-xo.html
This video demonstrates the techniques that will work with Excel 365, and also earlier versions.
Thank you so much for the video, it is amazing! I just got stuck at one point, I have at my table a column with dates and in my formula I need to insert only dates from a specific period, but I can not make it work using countif. Is there any chance you could please help me out? I have tried countif(">="&Z10;$A$2:$A$20) and many other ways, but it did not work. Z10 is just a regular date in the same padron (short date).
Hi Mark, I have a additional requirement over and above the multiple slicers + multiple criteria solution you have given. Is there a way that I get the entire row or rows filtered out instead of getting the sum of the values.
Tnx 4 nice video , just in multiple values text when I wrote formula gives a #Name error! !
Which version of Excel have you got? TEXTJOIN is currently only available in Excel 2019 and Excel 365.
@@ExcelOffTheGridhello excel 2016
I've been trying to make this work but since I have multiple criteria's I kept on having errors. -__-
For multiple selection in a single slicer i applied countifs and added sum but not getting the value when I select multiple data in slicer, please help
Hello Mark, i see you are using the slicer to filter by sales rep and then do a sumif. Is it possible to do a countif instead?
My issue is that i’m trying to use the slicer to filter by month (data column is as dd/mm/yyyy) and then do a countif of values.
Any insight would be very much appreciated.
In the video, I'm showing the principles to follow. It should work with COUNTIF. For you specific scenario, you may need to use the MONTH function to extract the month of the dd/mm/yyyy column.
Hello Mark, in my case is that i'm trying to use the slicer to filter date range
COUNTIF("
When I try to insert any slices on any tables, a box comes up asking for "Existing Connections" asking to select a connection or this workbook's Data Model and there is nothing to select to proceed. Your thoughts please.
Ciao, the problem could be that you work with a very old version of office or with a Mac.
Try to create a pivot table instead of a normal table and add a slicer this way.
Thank you, I am facing performance issue when I used multiple slicer .
Can u plz tell for fix this issue.
Performance is a known issue when using a lot of array formulas, as the number of calculations performed by Excel in the background increases significantly.
Given that most of this effect is formula driven, it comes down to your skills with picking and using the most efficient formulas for a given scenario.
advance use of basic functions!
Exactly - that’s the skill. Knowing how to use them together is the key.
Hello guys! I need a little help : when i do sumifs formula ( without the pivot with slicer criteria ) the formula it's work.But when i add the range and criteria for the pivot ( in the exemple for sales rep ) i have an #SPILL! error. Can you help me ?
The #SPILL! error most likely means
- There are some cells blocking the spill range (check out this video for a better understanding of Dynamic Arrays)
- You are trying to using the formula inside an Excel Table
👍👍👍