The COUNTIF function enables us to count the number of cells in a given range which satisfy a condition. COUNTIFS does exactly the same. However, with COUNTIFS, we can specify multiple criteria. For instance, let's say we have a worksheet containing four columns: the date, the number of phone calls received, the number of complaints and the percentage of calls which were actually complaints.
We now want to create a report worksheet to calculate the number of days where the percentage of calls that were complaints is, firstly, under 5%; secondly, between five and 10% and, thirdly, over 10%.
To work out those days where we have fewer than 5% or more than 10%, we can use COUNTIF function because we are dealing with a simple condition: less than 5% or greater than 10%. However, to calculate the number of days where we had between 5% and 10%, we will need multiple criteria; firstly, greater than or equal to five and, secondly, less than or equal to 10. We will therefore need the COUNTIFS function.
When creating formulas in Excel 2007, it is always useful to name the cells are you are referencing. To get Excel to create the names for you automatically, select all of your data, including the column headings then, in the Formulas Tab of the Excel Ribbon, click on Create from Selection. Next, activate the option "Create names from values in the Top Row" and click OK. Let's assume that the heading at the top of the fourth column (the one containing the percentage of calls which were actually complaints) is "Percentage", this will be the name that we will use in our COUNTIF formulas.
Next, we can switch to the Summary worksheet and click in the cell where we want to calculate the total number of days where less than 5% of our calls were complaints. Here we would enter the formula =COUNTIF(Percentage,"<5"). Notice that, when using COUNTIF and COUNTIFS, all criteria must be surrounded by quotation marks.
In exactly the same way, to calculate the total number of days where more than 10% of our calls were complaints, we would use the formula =COUNTIF(Percentage,">10").
Finally, to calculate the total number of days where between 5 and 10% of our calls were complaints, we would use the formula =COUNTIFS(Percentage,">=5",Percentage,"<=10"). The COUNTIFS function allows you to repeat the two arguments used with the COUNTIF function (criteria range and criteria) up to 127 times. Thus, in our example, criteria range 1 is Percentage; criteria 1 is ">=5"; criteria range 2 is also Percentage; and criteria 2 is "<=10".
We now want to create a report worksheet to calculate the number of days where the percentage of calls that were complaints is, firstly, under 5%; secondly, between five and 10% and, thirdly, over 10%.
To work out those days where we have fewer than 5% or more than 10%, we can use COUNTIF function because we are dealing with a simple condition: less than 5% or greater than 10%. However, to calculate the number of days where we had between 5% and 10%, we will need multiple criteria; firstly, greater than or equal to five and, secondly, less than or equal to 10. We will therefore need the COUNTIFS function.
When creating formulas in Excel 2007, it is always useful to name the cells are you are referencing. To get Excel to create the names for you automatically, select all of your data, including the column headings then, in the Formulas Tab of the Excel Ribbon, click on Create from Selection. Next, activate the option "Create names from values in the Top Row" and click OK. Let's assume that the heading at the top of the fourth column (the one containing the percentage of calls which were actually complaints) is "Percentage", this will be the name that we will use in our COUNTIF formulas.
Next, we can switch to the Summary worksheet and click in the cell where we want to calculate the total number of days where less than 5% of our calls were complaints. Here we would enter the formula =COUNTIF(Percentage,"<5"). Notice that, when using COUNTIF and COUNTIFS, all criteria must be surrounded by quotation marks.
In exactly the same way, to calculate the total number of days where more than 10% of our calls were complaints, we would use the formula =COUNTIF(Percentage,">10").
Finally, to calculate the total number of days where between 5 and 10% of our calls were complaints, we would use the formula =COUNTIFS(Percentage,">=5",Percentage,"<=10"). The COUNTIFS function allows you to repeat the two arguments used with the COUNTIF function (criteria range and criteria) up to 127 times. Thus, in our example, criteria range 1 is Percentage; criteria 1 is ">=5"; criteria range 2 is also Percentage; and criteria 2 is "<=10".
About the Author:
The writer of this article is a developer and trainer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel 2007 training courses in London and throughout the UK.
This entry was posted on 9:45 AM You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Posts
0 Comments