Today, we are counting sums, averages and number of transactions with more than one condition. In our example, we have transaction sales table and we want to count the values for given months. However, the months are written as the first day of the month and the last day of the month. It means that we have to add two conditions in order to count it. Let’s start with sums. We can use the SUMIFS function. The first argument of this function is a range on which we will be summing. In our case it’s the sales column so we have to select cell C2 and press the Ctrl + Shift + ↓ combination, then the F4 key to lock it (Fig. 1).
=SUMIFS($C$2:$C$1193
SUMIFS AVERAGEIFS and COUNTIFS functions SUM with conditionsFig. 1 SUMIFS function with a locked range
We write a comma, and then we are going to the criteria range 1. This will be the range where we will be checking the first criterion, which is the third argument of the SUMIFS function. In our case, we are checking dates, so let’s press cell A2, Ctrl + Shift + ↓, then the F4 key. We are adding a comma again, and now we have to write the first condition, which is the first criterion. The order of our conditions is not important because, in the end, all conditions have to be met to count the thing we want. Let’s start with the From date. We want values that are bigger or equal to this date, which means that we have to write the symbols of larger than and equal in double quotes, then an ampersand because we are combining this text with the value from cell E2 (Fig. 2).
=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2
Fig. 2 SUMIFS with the first condition
After pressing the F9 key, we can see that Excel changed the date into a number. This makes Excel check our condition properly (Fig. 3).
=SUMIFS($C$2:$C$1193,$A$2:$A$1193,“>=43831”
Fig. 3 Excel changed the dates
However, let’s move back to previous text by pressing Ctrl + Z. Now, we have the first condition. We can see that the second condition is necessary with second critirion because the next two arguments are in square brackets (Fig. 4).
=SUMIFS($C$2:$C$1193,$A$2:$A$1193,”>=”&E2
Fig. 4 Arguments in square brackets
Criteria range 2 in our case is the same as criteria range 1, so we can just copy it (Ctrl +C, Ctrl +V), but we really need to put it because each criteria range is working just with the argument of its criteria. It means that criteria range 1 is working with criteria 1, and criteria range 2 is working with criteria 2. In our case, the criteria 2 are numbers smaller than or equal to the last day of the month in cell F2.
When we want to sum the values from the sum range, the first and the second condition have to be met. Let’s put the formula and copy it down. We can see that at the end everything is working properly because we have written the formula properly (Fig. 6).
Fig. 6 Proper results
We still have some more values to count. Let’s take the averages. Since I’m a lazy person, I will copy the whole formula in edit mode, then go to the Average column, paste it and change only the name of SUMIFS into AVERAGEIFS. After entering it, we can see that only the sum range changed its name into average range. The remaining argument names stayed the same. It’s because the SUMIFS and the AVERAGEIFS formulas have the same syntax, but they count sums and averages correspondingly (Fig. 7).
Fig. 7 Function name change
Let’s put the formula into our cell and copy it down. We have the results (Fig 8).
Fig. 8 SUMIFS reults
Let’s now count the number of transactions. We will use the COUNTIFS function. This function doesn’t have any range on which we could count sums, averages and so on. It only needs criteria ranges and criteria. Our first criteria was the one with dates. So, let’s write A2, then press Ctrl + Shift + ↓, then the F4 key to lock it and a comma. I want to show you that the order of conditions is not important. Let’s take values smaller that or equal to the date in cell F2, comma, then the same criteria range, then the second criteria will be greater than or equal to the value from cell E2. Those are all the arguments we need for a COUNTIFS function (Fig. 9).
After copying the formula down, we have the results. We can check if our average is correct just by looking at it because it is just the sum divided by the number of transactions (Fig. 10).
Fig. 10 Checking the results
We can see that the numbers are exactly the same as in the Average column (Fig. 11).
Fig. 11 AVERAGEIFS and divide gives the same results
Today, we are going to talk about choosing a random element from a list without repeats. We have eight elements in our list. I put there the same number two times, which gives me the opportunity to choose the same number two times. If there are three the same numbers, I could choose the number up to three times. However, the choosing is random, so I don’t really know how many times I will choose it.
Random list no repeats, manual and formulas solutionsFig. 1 A list of elements
If we want to choose an element from the list at random without repeats, we can use a manual solution. We need to write the RAND function which returns numbers from 0 to 1 with fifteen digit precision. It means that it’s almost impossible to choose the same number twice (Fig. 2).
=RAND()
Fig. 2 RAND function
After dragging formula down, we have 8 random numbers. We have to remember that the RAND function is a volatile function. It means that it is recalculated every time our worksheet changes (Fig. 3).
Fig. 3 Recalculation
Now, that we have our helper column, we can do the sorting. We go to Data tab and we choose the A to Z or Z to A command.
Fig. 4 Sorting
If we want to have, e.g. five elements, we have to choose five elements from the top. It doesn’t matter whether we sort from A to Z or Z to A. We just choose the first five elements. We can see that the numbers are recalculated every time we sort our data (Fig. 5)
Fig. 5 Five elements from the list
Let’s get back to our original list and try the second solution. It is from Legacy Excel and it uses formulas. Let’s start with a way to sort our numbers. We can use, e.g. the LARGE function, which returns the biggest number from the list, then the second biggest, then the third biggest, and so on. We have to press the F4 key to lock our list. Then, we need a way to choose the first, the second, and so on numbers. So, we want to change our key argument using a proper function. One of the simplest solution I know is using the ROW function with reference to cell A1, which is free (without $ signs). When we copy our formula down, our A1 reference will also go down, and the ROW function will return the first row, the second, the third, and so on (fig. 6)
=LARGE($B$2:$B$9,ROW(A1))
Fig. 6 ROW function
After entering the formula, we have our numbers placed from the largest to the smallest. Our five elements are now in a proper order. It means that now we can choose elements. However, we must use the MATCH and INDEX function in our data orientation. Let’s add the MATCH function to find the position of our number. We have to add the range, which is our random number column and press the F4 key to lock it. Then, we are adding 0 because we want to have the exact match (Fig. 7)
=MATCH(LARGE($B$2:$B$9,ROW(A1)),$B$2:$B$9,0)
Fig. 7 MATCH function
After entering and copying down, we have positions of the largest numbers. When we look at the first position in our Chosen column, we have 5. It means that the largest number from our original list is located in the 5th place.
Fig. 8 Position of the largest number
However, we don’t want to have position numbers, but the elements connected to those numbers. It means that we have to add the INDEX function to our MATCH function. This time, we want to choose elements from our list, so we are adding the range and lock it by pressing the F4 key. It turns out that our MATCH function is the row number. We have to close the formula (Fig. 9)
After entering and copying down, we have elements selected at random without repeats. When I press the F9 key, I am forcing it to recalculate our results. Sometimes, we see our number (123) twice because it is twice on our original list (Fig. 10)
Fig. 10 Elements selected at random
It’s high time we tried the final solution in Dynamic Array Excel. This solution is simpler that the one in Legacy Excel because we just need two functions. In DA Excel, we have the RANDARRAY function which can create a random array. We just need to copy the same column into our formula, which means that we need an array with eight rows and one column. That’s why we are writing just 8 (Fig. 11).
=RANDARRAY(8)
Fig. 11 RANDARRAY function
After putting our formula into Excel, we have eight random numbers. In DA Excel, we can sort by this column. Thus, we have to add the SORTBY function to our formula. The array which we want to sort is our list. This time, we don’t have to lock it because it is a DA formula, and our formula is the result (Fig. 12).
=SORTBY(A2:A9,RANDARRAY(8))
Fig. 12 SORTBY function
After putting the formula down, we have eight elements from our list in a random order. Pressing the F9 key allows us to choose elements once again at random. Let’s choose five elements from the top once again. We still can see the remaining elements. If you don’t want to see them, you have some options. You can, for example, select five elements from our list, then press the F2 key to go to the formula edit mode, then press the Ctrl + Shift + Enter combination to create an array formula. The array formula is from Legacy Excel. Now, we have only five elements because I’ve selected only five cells. Those cells are an array, which means that we cannot change or delete any single element of the array. If you want to change it, you have to change the whole array at one time (Fig. 13).
Fig. 13 An array
Summing up, if you want to choose an element at random from a list, you can use a manual solution, a Legacy Excel solution or a Dynamic Array solution.
