The Microsoft Excel function we’ll learn in this Excel help article is the RANDBETWEEN function. So what does the RANDBETWEEN function do? RANDBETWEEN generates random numbers between a range of numbers. This Excel function is very useful if you want to generate numeric data in a spreadsheet for testing purposes. The syntax of the function is RANDBETWEEN(bottom, top). So if we want to generate random numbers for say cells A1 to J50 and fill them up with random numbers from 1 to 100, enter the formula =RANDBETWEEN(1,100) in cell A1. Then hover over with your mouse to the bottom left of the cell till your mouse cursor changes into a plus button. Once it does click and drag till it covers all the cells till J50. The moment you release the mouse cursor RANDBETWEEN will fill the cells A1 to J50 with random numbers between 1 and 100. This will include the numbers 1 and 100 too.
The second function we’ll learn is the COUNTIF function. Using this function you can count the number of occurrences of a particular value or content in a cell range. This function is particularly useful in statistical computations. The format of the function is COUNTIF(range, criteria). So let us say we filled random numbers between 1 and 100 in the cell range A1 to J50 and we want to know how many times the number 18 is repeated. For this in an empty cell enter the formula =COUNTIF(A1:J50, 18) and instantly the number of times 18 is repeated in the selected range is reported. Note that instead of entering the range in the formula you can also select by clicking and dragging the entire range and it will automatically be inserted into the formula.
Now what if we wanted to add the number of 18’s in the range. To achieve this we’ll use another function, the SUMIF. The format of the SUMIF function is SUMIF(range, criteria, [sum-range]). So if we were to find out the total of all the 18’s in the range we created we need to enter the following formula in an empty cell of the spreadsheet – =SUMIF(A1:J50,18) and the total is instantly calculated.
To further enhance the power of the COUNTIF and SUMIF functions, Excel has introduced the COUNTIFS and a SUMIFS functions. We’ll explore them now.
Let us suppose we have a spreadsheet of details of orders and the sales they made. Suppose we want to find out the orders that made more than 500,000 dollars but less than a million dollars using the COUNTIF function then we would have to employ two COUNTIF functions as follows.
=COUNTIF($B$1:$B$ 100, “>=”, &L1) – COUNTIF($B$1:$B$ 100, “>”, &L2) where L1 and L2 have the values 500,000 and 999,999 in them respectively.
However using the newly introduced COUNTIFS we can achieve the same using just one COUNTIFS statement as follows.
=COUNTIFS($B$1:$B$ 100, “>=”, &L1, $B$1:$B$ 100, “>”, &L2)
The format of the COUNTIFS is:
COUNTIFS(Criteria_range1, Criteria1, [Criteria_range2, Criteria2,], [Criteria_range3, Criteria3,]……..) COUNTIFS can have 127 combinations of the criteria. Even wildcards can be used.
Excel introduced the SUMIFS in version 2007 it is a superior form of the SUMIF function. While the SUMIF can take only one criterion the SUMIFS can take multiple criteria. The SUMIFS work very much like the COUNTIFS function, in that that multiple criteria can be used.
Using our orders example suppose we also have state and country information and we would like to sum all the order totals of orders in U.S. and from the state of Iowa we can do so using the SUMIFS function.
The SUMIFS has the following syntax SUMIFS(sum_range, criteria_range1, criteria1, [ criteria_range2, criteria2], [criteria_range3, criteria3]….)
And the SUMIFS for our particular problem would something like,
=SUMIFS(B1:B 100, C1:C 100, L1, ) where B1 to B100 represents the order values that need to be summed up, C1 to C100 contain the values for the various countries and L1 contains the U.S. (country). For the second condition i.e state of Iowa, the following also goes into the function.
=SUMIFS(B1:B 100, C1:C 100, L1, D1:D100, L2) where D1:D100 contains list of States and L2 contains the name of the state, Iowa.
The free Excel help article series comes to you out of the corporate office in the Irvine area of Orange County California. New articles are added weekly. Please check back for additional articles on this as well as other topics. You may also suggest an article that is not covered, simply call or e-mail, and we will write it and add it for you. And if you want to get ahead of the curve, Excel 2013 (Excel 15) is nearing its release date. There are many spectacular changes, many of which you want to use. we can upgrade your current system to Office 2013, we can automate your work, and we can leverage what is new in Excel. We can even come onsite and train your staff on the changes, or we can do so remotely.