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).
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?
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. 😀
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
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 👍
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.
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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-TCwec8-tsDk.html This video demonstrates the techniques that will work with Excel 365, and also earlier versions.
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.
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
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.
