With the launch of Microsoft 365 including cloud services and the new lowered-priced subscription options, Microsoft sales are booming again and with over one billion installations of Microsoft Office, proper training in the Microsoft suite of applications has become essential. For a great overview of what Office 2013 has to offer, sign up for the Office 2013 For Dummies Video Training, Deluxe Edition course from Udemy today and get up to speed with the new functionalities offered by Office 2013.
This tutorial is designed as a step by step guide on how to use the COUNTIFS function in your worksheets and spreadsheets. We have set up a worksheet for the purposes of this tutorial. The worksheet contains sales data from a fictitious company including customer names, salesperson’s name, the type of item sold, the units sold and the total sales value per sale. Our worksheet for the purposes of this tutorial looks like this:
The COUNTIFS function is very similar to the COUNTIF function. For a step by step tutorial on how to use the COUNTIF function, you can read: An Excel COUNTIF Tutorial to Show You How to Use COUNTIF in your spreadsheets on the Udemy blog.
Let’s see how we can use the COUNTIFS function in our worksheet to extract the sales data we require.
How to use Excel Functions
Excel allows you to access the built-in functions by typing the function name within the cell where you want the answer to appear, or you can use the formulas tab to access the built-in functions and then select the function you want to use from the formula tab. For the purposes of this tutorial, we will use the function from the formulas tab to show you how to enter function arguments via the function wizard. Sign up for the Excel 2013: Introducing Excel course today to learn to use the built-in functions offered by Excel.
To use a formula or function in Excel, you need to know how the function works and what the function does. For an article on functions, you can read Excel Tutorial: An In-Depth Guide to Working with your Data, Building Formulas, and Using Functions available from the Udemy blog.
The COUNTIFS function uses parameters you enter in the function to count the number of occurrences of data that meet your specific requirements within the range of data you specify.
Using COUNTIFS to Calculate Jack’s Sales on a Given Day
Our first example will show you how to find out how many sales Jack made on a specific date. Assume we need to find out how many sales Jack made on the 7th of February.
To count how many sales Jack made, we need to access the COUNTIFS function and use it to count the number of sales for February 7th and also how many of those sales were made by Jack. To create the function, click the target cell you want the answer to show in and then click the formulas tab and then select the COUNTIFS function from the “More Functions”, “Statistical” menu.
You need to select the Date range for the first criteria so that Excel searches and counts the number of date occurrences within that range and then you need to set the criteria to the date you want to search for. In this case we set our date range as =B10 because B10 contains the value of the date we want to search for.
You also need to select the range that contains the salesperson’s name and then set the criteria to search for as Jack, so that we count the number of sales made on the 7th of February by Jack.
The result of the above COUNTIFS formula will be 1. You can see from the data that Jack made only one sale on the 7th:
Use COUNTIFS to Count how many Items were sold between two dates
In our next example, we will create a few cells to allow us to quickly search for sales for a specific item between two dates. We have created named ranges for different columns of data. We called the Date column “dates”, we named the Salesperson column “Salespersons”, and we named the Unit type column “Types”. For an article on cell references and how to create named ranges, you can read Excel Cell References and How to Use Them in Your Worksheets and Formulas on the Udemy blog and for a course on how to create named ranges and how to get the best from your Excel, sign up for Use Excel Like A Pro. Fast from Udemy.
Here are the cells we have added to our worksheet:
We can now use these cells in our COUNTIFS formula to quickly and easy count data within our worksheet.
To create the COUNTIFS formula, select the COUNTIFS formula from the formulas tab and then fill it in with the following data:
Because we are working with dates, you need to refer to the absolute reference of the two date cells we are using for our search. For an advanced course on Excel, sign up for the Microsoft Excel 2013 Advanced Online Excel Training Course from Udemy.
The “dates” refers to the named range we created for our dates and the “Types” refers to the named range we created for our sale types.
By adding these fields, searches are simpler in future. All you need to do is change the start and end dates and change the type to see the sales of a particular item between two dates.
This is what the result of the above COUNTIFS function will look like:
For an advanced course that will teach you to harness the power of Excel function and formulas in your spreadsheets, sign up for the Mastering Excel with MS-2007, 2010, 2013 course available from Udemy today.