Excel Array Formula: Count Rows with OR condition - SUMPRODUCT & FREQUENCY Functions (Part 1/3)

the aim of this lecture is to show you a simple method that you can use when you need to create more complex array formulas what we are going to do is get a unique challenge across rows so not columns but rows and the unique challenge is based on the criteria that you can see here so I don't just want to give you a recipe to use for these situations but what I want to do is show you my steps of coming up with more complex formulas because watching my steps and understanding how array formulas work would actually help you the next time you're trying to build a more complex formula from scratch so for me personally Mike Gervin has been a great inspiration in learning and working with array formulas so I do suggest that you check out his videos at excel is fun YouTube channel let's take a look at this analysis I have here a list of workshops so they go all the way down to 53 different companies can take part in the workshops and they can win prices one company can win more than one price you can see Company D got first place and third place the question that I need to answer is how many workshops did either of these companies win says BD or R what this means is that this line where I have B and R counts as one this line where I have d2 x also counts as one so this would be a 1/1 these would be a zero because they're not on my list so if I was only looking at these four events the answer would be to our ultimate goal here is to get the answer with one formula like in this one you so this might look a bit complex but don't worry about it right now because I'll be describing each step in detail till we get to this stage and I'm going to take you through my process of how I come up with this formula because I for sure don't start just writing it out like equals on products and put in frequency or a memos and no way I start somewhere in the middle and then I build on it so I don't even know what other functions I might need at this stage I wait I get stuck and then I try to think what function could help me to transform this matrix to take me to the next stage now with Excel array formulas you can use different ingredients to get to the same result but you need to have those ingredients on hand and by having ingredients on hand I mean that you should know some important functions that work well in arrays for example the row and column formula the matrix multiplication formula frequency transpose and so on they really work great in arrays because they can manipulate matrices to give you the result that you need in the end and if you aren't that familiar with these functions don't worry I'll try to cover them when I take you through the steps as a first step I'm going to solve this using a helper column that's the easier method in the helper column what I ultimately want to get is what I typed in before I want to get a 1 1 and a 0 0 here to do that what I can do is use the countif function I can take a look at this range and take a look at this one first so if there is a match in this case my answer would be 1 right I would then add it to the count if again this range and cross-check with the second one it's because I have an or condition I need to use the plus here same for the last one in this case I get to because I have these two companies on my list now how do I get a one out of this and not a two I can use the min function get the minimum of one and the results of this count is in this case my answer would be one right before I pull this down to check the other ones let's fix these with a four that's something I usually forget doing these cases I get a zero because main one zero very smaller I could then just sum this up and I'll have the answer 29:29 workshops were either of these companies one another way of writing this actually my preferred way of writing it I'm just going to put helper B and helper a is to use my favorite formula which is to pry surprise the from product formula so how can we use some product like this I'm going to check this range against this range and this is something I'm going to fix right now and bracket closed now if you watched my son products video I always say the moment you're putting your equal sign in their open brackets again I'm going to do that right now it doesn't really make sense at this moment because I'm just putting a bracket in the bracket but it's going to make sense in a second to zero it's not what I'm looking for right let's take a look at what's happening here we're going to go to evaluate formula what is it doing it has generated a matrix of true and false values notice the comma and the semicolon the semicolon means move to the next line in the matrix of the next row and the way its filling this comparison is that it checks this against this that's my true then it checks are against B again that's the false and K gives B and then it moves to the next row and does B against T or against T and so on and then when it comes to our against our you get the true again that you see here the comma and the semicolon become really important when we try to write this as one formula later on the zero means that well it can't add up true and false values what we need to do is to perform some type of numeric operation on this because a numeric operation translates the true to a one and the false to zero so I can do x one that's a numeric operation but a more elegant way of writing this is to use double negation double minus signs this result is what I got before with my counter function and now I can wrap this up in the min function just the way I had before and I get the same result with less text okay so we can just try it here and from this column as well now comes the difficult part but also the fun part we're going to write one formula that helps us get here let me show you how I approach this the first thing I do is to decide on a main function that I think I can use here that function for me is sum product because that seems to be the solution of many of the lookups I've done in the past ways the reason I choose some products in this specific case is also because I have this condition and I have this checking it doesn't mean that that's going to be the final formula that I think is going to be optimal it's just going to be my starter formula the next thing I do is I break down the data set I don't do it on this entire set because the matrix becomes too big and I can't really understand what it's doing because there's a lot of data to take a sample of the data and try to make the formula work on the sample and when it does I just expand it to the rest that's the part I'm going to take I'll just put a border on there so that we know that we're only doing their operations until here the answer we're looking for is for if I take this data sample I'm going to write the draft formula here start with some product I'm going to open bracket again because I'm doing the comparison and now that's my range and I'm going to compare it to this bracket close a plus because we're going for or condition that is do the same for the next one we get 6 why because it's doing a 1 1 here so that's 2 3 4 and we have a mass number here that's a 5 and that's the fixed it doesn't give me 0 in this case why because I'm doing an operation on them that's forcing it to translate that true and false here two ones and zeroes and we can actually see that if we go back to formulas and reevaluate formula and we step in can see these true/false values here and the next step it's doing the true/false for the next round of matrix you can see here and now because it's forced to add these together it's going to translate them to ones and zeros and then it does the same for the last one and we get our final matrix and if you go and add these we end up with six now this is the stage that I kind of get stuck and think well how can I take this to the next level a my approach is to get this matrix out and paste it in Excel so I can really visualize it the way to do that is to press f9 so we get our array I'm going to press ctrl C and then I'm going to press escape to leave I'm going to highlight this range that has to be identical to this range the size of it has to be identical to this range and I'm going to put equals paste that in and now not answer about control shift enter so I bring the values in here I'm just going to paste these as values to take away the formula now I have something that I can grasp I have something that I can work with this is my starting point the end point that I want to be is that this is one one these are basically zeros and this is a one one that's where I want to go this means that my matrix is going to change instead of a 7 by 3 matrix I'm going to have a 7 by 1 matrix this is where your toolkit comes in what formula what function can I use to actually change the format of this matrix from this to this the first formula that comes to my mind is the frequency function another formula that comes to my mind is the matrix multiplication function in this case I'm going to try and use the frequency function first I cover the frequency formula in my Excel online Advanced Course but if you forgot what it does I'm going to take you through the steps in a bit I know that that's my starting point but that's my ending point but I need to do something in between to get here and that is I need to make these lines unique there's no way that I can identify that this is a different one this is a different line to this line here they're all one so just go through and analyzes them but if I managed to make this line different to this one so for example I would have two here and if there was a match here this would be a three and this would be a six and this would be a seven I have the ability of looking at these rows separately as the next step I can use the frequency function to see how many ones I have how many twos I have how many three four and so on that should give me a matrix that looks like this and consists of numbers and zeros so basically takes us to where we were before we put the min function here first I need to get here the way I can get here is to use the role function and that means I need to multiply this but not only this matrix I need to multiply this entire matrix the entire result and that means I need to put a bracket over this part and I multiply it by the role function the result is 37 why let's have a quick look we're going to do F 9 well this belongs to Row 4 so I've practically changed these two for this to 5 this 1 to a 9 and this one to attend because it's the road that they're sitting on but I prefer to have this as one tools and so on so I'm discriminate force escape to leave I'm going to make an adjustment to this and deduct the first role here and because I don't want to start with 0 and start with 1 I'm going to add a 1 so I'm going to quit this part in bracket because I want the results of this to be multiplied with this gets a bit looks better let's take a look that's what we get here so 1 1 0 and that's this one and the 6 and the 7 is here we have got to this stage so let's escape and go out now I'm going to use the frequency function to get a matrix that is 7 by 1 how does the frequency function work I'll give you a quick summary here what frequency does is it looks through your data range and it counts the number of occurrences each of the data have and puts it inside certain categories it's called bins in Excel that you define for example if my category was one and seven okay let's use the frequency function and see what we get frequency is an array formula that means that you need to press control shift enter if you're writing it on its own here and you need to highlight the cells that are going to hold your answer going to highlight this and I'll tell you in a second why I'm highlighting an extra one we start off by writing frequency first of the data array that our matrix here second is our interval which is this control shift enter for one I get 17 do I have 17 one no but I have 17 one n zeros so the way it works is that anything up and including one is what is being counted next is 2 to 7 so excludes one and includes 7 between two and seven and I have four because that's these ones and the zero here is anything above 7 which I don't have in this case if I change this to a six I get one here and this changes as well how can I use this in my formula well I can't have my bins starting from one I need them to start from zero because I need to strip out the zeros from here so I can have zero one and go all the way to 6 see this is the one I need to change and control shift enter so I'll end up with a matrix like this and you see that when I'm typing this in Excel I have to highlight an extra cell but when you use frequency inside an array formula Excel does this extra one for you automatically once they get to this level all I have to do is check is this number above zero and to take it and change it to ones and zeros I will have a 1 for the zeros so in the end I will need to strip out one from my final answer to strip out the zero so what this means is that I need to have at least one appearance where none of these companies want at least one cell that holds another company in this analysis and 100% sure that I will have that because that's why I'm doing the analysis otherwise my answer would always be the count of workshops if only these three companies we're always taking part and always winning and there was no other company winning so I can use this formula with a good conscience we are at this stage we're going to use the frequency function to get to this stage all I have to do is to wrap this result in the frequency formula going to open bracket here and now we need the bim's array the bim's array to get it dynamic in Excel it's basically this formula just without the plus one put another bracket that's 21 so that should be this sum I've actually ended up creating this matrix right now and we can have a look at it by highlighting this and pressing f9 the next step is to come here than to do that all I have to do is to check if this is greater than zero now is that enough so let's see what happened f9 and we get a bunch of true and false values that this is true true true false false false and two true yes I'm just going to press ctrl-z and to translate this true and false values to ones and zeros I can put the double negation here and we get five since I've written this one now in front of the 15 here this one now it's no longer in sync with this so let's just do that so this is the stage that we're at with our matrix all we have to do is to strip out this one appearance of the zero and then we end up with this number when you write the formula like this you write it from scratch you are fully aware of its bugs of its limitations and now we know what the limitation is that we do need to have one company that's not on this list in one of these 21 cells if it's not the case we need to create another formula my first approach when I was given this problem was to use this and then when they came across this limitation I thought okay I actually want to make this 100 percent robust so I'm going to use matrix multiplication and that's what I'm going to show you in the next lecture as the last step here all I do is to copy this here and change all the tens in this case I don't have tens anywhere else 253 I'm just going to highlight these two I'm going to do control age 10 and 53 that's the same number we have there in the next lecture I'm going to take you through the matrix multiplication method and we're going to see why it's more robust than this method these are basically the steps you can follow when you create more complex formulas and I personally find that breaking down the array in this way it really helps me follow my formula like follow it in its footsteps and it helps me figure out how I can take it to the next level like which formula or function could I plug in now to transform the matrix to what I need it also helps me understand its limitations so if for some reason it doesn't work I can know I know where to find that bug thank you for watching and don't forget to subscribe

Channel: Leila Gharani

Views: 78,749

Rating: 4.8704858 out of 5

Keywords: Excel Array Formulas, array functions, sumproduct formula, Sumproduct, frequency function, Row function, count rows, OR condition, Frequency function, XelplusVis, Advanced Excel, OR logical test, Master Excel, Learn Excel Formulas, Excel formulas, Excel Tips, Excel Tips and Tricks, Best Excel Online Course, Excel Advanced formulas, Improve Excel skills, Excel for analysts, Excel for controllers, Microsoft Excel expert, Leila Gharani

Id: Nfu0fvbnccU

Length: 23min 5sec (1385 seconds)

Published: Tue Jan 17 2017