The COUNTIF Formula is used to count the number of cells that match a specific condition. It's hands down a game changer of a formula because at it's very basic it can give you some basic stats on data but as you'll see when we delve deeper that as the level of sophistication improves so does it's power at analysing.
But...let's start at the basics, the Formula itself is entered as
and is found under the FORMULAS tab in the Ribbon. Click More Functions, select Statistical and scroll down to COUNTIF.
The range entered will be the area in which Excel will search for the condition and may be manually entered or entered by highlighting the area within the worksheet that contains the data.
Conditions set by the user could be an exact wording match, counting the cells that match an individual cell, could be multiple conditions, or it could be a greater than, less than, or in between condition. The COUNTIF Function is useful to quickly count the number of customers in a geographic region or find how many types of inventory one has or even find how many products are currently being sold at a price between $1.00 and $5.00.
Consider the following worksheet as we delve into the various conditions used within the COUNTIF Function.
Counting Cells with an exact wording match
If you wanted to know how many Projects are Renovation Projects you could easily scan hundreds of projects at once by entering the formula into any unused cell, typing
Keep in mind that conditions are not case sensitive. Entering in “renovation” will yield the same result.
The range for this example could be the entire worksheet or in this case we are searching down the second column to count how many times “Renovation” is entered and the range could be narrowed down to just that column. The result in this instance would be 4. This condition works successfully if all data is accurate and consistent.
Counting Cells that Match
If you wanted to know how many Projects are located in New York City you could use the same formula as above and type in “New York City” as the condition or you can click on a Cell that contains the word “New York City” which in this case is A2 and Excel will match all other cells and total the findings. This formula would be entered into any unused cell as =COUNTIF(A2:C9),A2) hit enter and the result will yield a 2.
Counting Cells using a Greater Than or Less Than Formula
To find the number of Projects that cost more than $10,000 use the formula,
Be sure to enter the quotes before and after the condition and then close out the parenthesis. The result will be 5 in this case. Similarly, if the desired result is for all items priced equal to or greater than $10000 the condition would be entered as “>=10000” resulting in a value of 6.
The less than formula is the same as the greater than formula except the condition would be entered as “<10000” or for less than or equal to “<=10000”.
This formula could also be written with a match cells formula instead of the exact wording. =COUNTIF(A2:C9,”>”&C5)
The operator (>) must be enclosed in quotes and immediately followed by the ampersand sign then the cell that contains the data, in this case C5.
Counting Cells in between two values
Should you want to locate the total Projects that have a price range of $10,000 to $20,000 the formula would be:
that is excel is searching for all items equal to or greater than $10,000 and subtracting all items greater than $20,000 to show only those items with prices ranging from ten to twenty thousand.
Counting Cells that do not equal a specific amount
Say we wanted to find the number of Projects within the United States. We do not have a Column that lists the Country the Project will be completed in but we can quickly see that the only city outside of the United States is London. We can now use the formula to find the total number of Projects that are NOT in London by entering
The end result is 7 Projects within the United States.
Building off the original worksheet from here on let’s continue with the following data
Using wild cards
Notice in the worksheet that Project Type is usually written out as Renovation, however, in Row 9 it is written as “Reno”. Inconsistent data is all too common in large spreadsheets making it impossible to count the number of Renovation Projects by using either the exact wording match or the match cells conditions used earlier. If the data is inconsistent Wild Cards may be utilised.
The first wild card is the asterisk (*) The asterisk is used to find cells that have the same beginning or ending characters. To find the number of Renovation Projects enter:
this formula is telling Excel to search Column B for any Projects that begin with “Reno” and under the Counts total in the above screenshot the number is 4.
The second wild card character is the question mark (?). The question mark is used if you need to find the count of cells that you know either begin with or end with specific letters or numbers and have an exact same amount of characters. To see the total Projects in London you could enter the exact wording match, match cells or you could enter:
With this Excel will search for any 6 letter words ending in ON within Column A. Should your data have an asterisk or question mark within the cells the COUNTIF Formula can still be used by inserting a tilde (~) in front of the question mark or asterisk in the formula. Excel will then treat the question mark or asterisk as an actual character versus a wild card.
Multiple Conditions
Using the addition sign (+) tells Excel to search for more than one condition.
To determine the number of permits that must be completed during the fourth quarter, enter:
Telling Excel you want the total count for all Permits with a due date of October, November and December. Out of the 6 permits due, 4 are due within the 4th Quarter.
Working with dates using the COUNTIF Function
The formulas that have already been mentioned will work the same using dates. Plus you can combine the COUNTIF Function with Excel Date and Time functions to find the number of projects that have already started as of today, the number of projects that have not started as of today or the number of projects that started in the first quarter. Using the Start Date data we enter those formulas using the TODAY() Formula as such:
Number of Projects that have not started as of today =COUNTIF(C2:C11, ">"&TODAY()) Showing 3 Future Projects.
Number of Projects whose Start Date has passed =COUNTIF(C2:C11,"<"&TODAY()) Showing 7 Past or Present Projects.
To find dates within a specific range utilise the Counting Cells in between two values formula as previously mentioned and enter the dates as the values. For all Projects with a Start Date during the first quarter:
Showing 7 total Projects started during the first quarter.
Using COUNTIF Function to help clean up data
To find the number of mistakes within a worksheet without having to scroll through all the data you could utilise COUNTIF Functions to check for mistakes.
Finding missing data:
The formula above lists all blank cells within the entire worksheet, however, by searching each column one at a time you can skip over the columns without blank cells. Go through the columns that yield a count of blank cells and enter in the appropriate data. Likewise, removing the asterisk from the equation will provide the total number of cells that are not blank.
Finding duplicate data:
COUNTIF can find duplicates within one or multiple columns and rows. For example, let’s assume the company never has a project start on the same day. Search the worksheet for repeat dates. The simplest way is to add a column to the worksheet titled duplicates. The formula to test for duplicates is:
Excel is Searching all of Column C for any date that matches the date in C2. Repeat this process down the Duplicate column and any TRUE values can easily be located as duplicates. The result shows a duplication of C5 and C12. To quickly search for the TRUE values, Sort the data in Column E from Z to A and all duplicates will be at the top of the worksheet. If necessary, all duplicates can be deleted.
To get a total of all duplicates enter:
Now, i'm about to get a lot more complicated but just trust the formula! Let's Count duplicates in multiple columns, we enter the following:
This results in a 1 showing one single duplicate between Column A and B. That duplicate is Tampa accidentally being entered as the Project Type and can easily be remedied. This same duplicate could have been found by searching for duplicates within a row by entering:
Repeat for each row to find any duplicates within the rows.
Hopefully, these examples on how to utilise the COUNTIF Function have shown the wide array of uses. If you run into any problems with the formulas be sure to check the following common mistakes.
• Make sure there are no spaces between any characters in the formula
• Double check that the parentheses have been closed
• Ensure the operators are enclosed in quotations
I hope you found the article useful, let me know if you have any questions and before you head, sign up below for my free book 'The only 27 Formulas a professional needs to know'.
Sohail
The post Using the COUNTIF function in Excel: Beginner to Advanced appeared first on Earn and Excel.
No comments:
Post a Comment