How do I Find the Mean in Excel?

In Excel, the mean is referred to as the average. To find the mean in Excel of a set of numbers you add all numbers within the group and divide the total by the count of those numbers. This is a concept that our Excel classes cover in great detail.

In the business sector, the mean is the most commonly used measure to find the average of a set of values. Take sales for example, it’s not uncommon to use the average sales from a previous year to predict future years’ sales.

However, the mean is only as accurate as the data being measured. If there are outliers in either direction they will seriously distort the average pulling it more to the side with an outlier.

The formula or syntax to find the mean in Excel is =AVERAGE(number1,number2…). You can manually enter the numbers into the formula or you may select a range of cells that have the numbers within them. The formula can also be found in the formulas tab within the ribbon, select more functions and average is in the statistical menu.

How Can I Find the Average in A Row?

In the following example in this Excel tutorial, we are evaluating our top ten salespeople on their quarterly sales for the year.

To find Amy A’s average sales for the year we could enter =AVERAGE(2500,3850,3500,5500) or =AVERAGE(B2:E2) both formulas would give us the same result of $3,837.50.

How Do I Find the Average in A Column?

To calculate the quarter 1 sales (column B) enter =AVERAGE(B2:B10) to get $4,200.00.

How Do I Find the Average in A Range?

To calculate the year’s average sales across all salespeople enter =AVERAGE(B2:E10) to get $3,655.56.

How Do I Find the Average in Non-Adjacent Cells?

To find the mean in Excel for the 1^st Quarter Sales across the North District enter =AVERAGE(C2,C4,C8) to get $4,433.33. As you can see in this Excel course example, finding the mean is not exceptionally tough.

How Do I Find the Average of a Range and Cell?

Let’s say Herb’s 4^th quarter sales were actually in the south district even though he’s normally within the east district. To find the average sales for the south district for the year we would need to include Herb H’s 4^th quarter sales. Enter =AVERAGE(C3:F3,F9,C10:F10) to get $4,266.67.

The average function can also be used to find the average percentage, average time, and dates.

How Do I Calculate the Mean in Excel Time?

We know the average 1^st quarter sales are $4,200.00 but what was the average travel time to get those sales?

=AVERAGE(D2:D10) shows an average travel time of 4 hours and 3 minutes.

How Do I Calculate the Average Percentage?

What is the average commission that the salespeople earn?

=AVERAGE(E2:E10) to see a 16% average commission.

How Do I Calculate the Average Date?

When do the salespeople typically make their first sale? =AVERAGE(F2:F10) shows the first sale is usually made by 1/31/17.

While using the average function is relatively simple and versatile there are a few types of data that will be ignored when Excel is making its calculations. This online Excel class covers this as well. Excel classes can teach you any of the fine points of Excel and its practical real-world applications.

• A blank cell will not be included in the average.

Note: entering a 0 within that blank cell will be included and may likely be an outlier amongst your data which will seriously alter your mean.

• Cells with text data will not be included in the calculations.
• Texts with boolean values, true or false, will not be included.
• Numbers that are written in text (one, two, three) will not be included.

Note: Using the average Function you can include written numbers and boolean values.

With these simple formulas, you should be able to calculate the mean in Excel. If you want to learn more complicated formulas, you should consider further Excel lessons.

Excel Classes Will Have the Program Doing the Math for You

Finding the mean of a range of values is something you probably learned in mathematics at school. But when you have a large spreadsheet filled with many values, it can be tough and time-consuming to calculate it all by hand. Excel classes teach students the formulas necessary to make quick work of these tasks. Online Excel courses are exceptionally efficient at teaching students how to make Excel work its hardest for them.

What is the Lookup Function in Excel?

Though there are a multitude of really great techniques to find information, the focus for today is the LOOKUP Function in Excel.  Our Excel classes cover this topic in great detail so that you can walk away with the greatest understanding of these concepts as possible.

If you are not entirely sure how to work with these lesser known formulas, I recommend going to the formula tab in the ribbon under lookup & reference as highlighted. Or take some online Excel courses to learn more features.

The LOOKUP Function in Excel should be used when you are searching a single row or column for one piece of data in order to retrieve another piece of information within the same position. For example, you know the stock code but want to know what the price of the stock is. You’d use LOOKUP to locate the stock code and retrieve the price. Our Excel classes will show you exactly how to do this.

The VLOOKUP, or vertical lookup function, should be used when you need to find data within the worksheet by row. This is similar to LOOKUP. You would use this to search by stock code to find the price by searching across the row. HLOOKUP, or horizontal lookup, is used when you want to search down columns for information. It starts by searching the first row for the value and then moves down the column to find the second value. This Excel tutorial will examine the three LOOKUP functions to see which is best during certain circumstances.

How Do I Select the LOOKUP Function in Excel?

When LOOKUP is selected from the lookup & reference button a dialogue box will appear asking which argument you will be using vector or array.

Vector is recommended when using the LOOKUP function in Excel. If you would like to use array it is strongly recommended you use the VLOOKUP or HLOOKUP functions instead. The vector form searches a specific vector (either one row or one column range) for a value and then returns a value from that same position in a different vector.  The formula for the function is: =LOOKUP(lookup_value,lookup_vector,return_vector)

How Do I Use the LOOKUP Function in Excel For Searching Down Columns?

Example 1: Find the code for “property”

Lookup value = property

Lookup vector = column A

Return vector = column B

=LOOKUP(“property”,A2:A20,B2:B20)

Example 2: What type of account is code 600

Lookup value = 600

Lookup vector = column B

Return vector = column C

=LOOKUP(600,B2:B20,C2:C20)

How Do I Use the LOOKUP Function in Excel For Searching Across Rows?

Example 1: Who didn’t have any sales during quarter 1?

Lookup value = 0

Lookup vector = row 4

Return vector = row 1

=LOOKUP(0,A4:F4,A1:F1)

The LOOKUP Function in Excel is a great function for quickly searching a worksheet to find a specific value.

Excel Classes Make Finding Data Easier Than Ever

When you have a large spreadsheet containing a lot of information, manually scanning the sheet for a specific value can take a lot of time. This is why the LOOKUP feature is so critical. In our Excel classes you will be taught the importance of using this feature, as well as how to apply your newfound skill in your professional career.

How May I Lock Columns in Excel?

Learning how to lock columns in Excel allows users to enter or edit information into all other cells except for those which have been locked. Hiding or locking cells has no effect at all on the worksheet until you protect the worksheet. Our Excel classes offer a tutorial on how to do this. Just continue reading the content below.

When sharing projects amongst several team members, sometimes it is necessary to lock certain cells within the worksheet to ensure the data isn’t deleted or changed. When the worksheet is shared, the team member will be able to adjust all unlocked cells, but the locked cells will remain the same. This is a valuable skill to have as you progress through your Excel training.

How to Lock Columns in Excel: Is It Easy?

While you can learn how to lock columns in Excel in Excel classes, it is fairly simple.

If the worksheet has already been protected, the first step is to unprotect it by clicking the review tab within the ribbon and selecting unprotect sheet. You may have to enter a password if the worksheet was protected with one originally. We have covered this subject in another Excel course article.

1. Select the whole worksheet by clicking on the select all arrow in the top left of the worksheet (highlighted in picture above).
2. Right click anywhere and select format cells from the menu options. Or, from the home tab, click the small arrow at the bottom right of the font box or you can simply enter (control + shift + F) to have the format cells box open.
3. In the protection tab of the format cells box, uncheck lock cells and click OK to complete unlock all cells within the worksheet so you can designate the specific cells within the worksheet that you want to lock.
4. Now back on the worksheet select the column(s) that you would like to lock. Open the format cells box again and this time click to put a check-mark back in the locked checkbox.
5. Go back to the review tab and select protect this worksheet.
6. A dialogue box will pop-up asking if you would like to password protect the worksheet. There are numerous elements that you can either allow or disallow users to do within the worksheet. The first two elements are selected by default:
a. Select lock cells – allows users to select cells that are locked but does not allow changes or deletion
b. Select unlock cells – allow users to select and make changes within all unlocked cells.
c. All other elements are in relation to formatting, sorting, inserting/deleting columns and rows and are not selected by default.
Once you click OK the worksheet will now be protected.
7. If someone attempts to make changes into a locked cell the following message will pop-up on the screen.

Can You Unlock Columns in Excel?

To unlock columns, you will need to begin by unprotecting the sheet under the Review tab. Select the column you have locked and bring up the format cells box. Under the protection tab once again unclick the locked checkbox. This is fairly similar to the process of password-protecting a workbook or sheet, as outlined in a previous Excel tutorial.

Learning how to unlock columns in Excel could also be beneficial at a networking event where each member could enter their information into a worksheet as shown to the left. Column A could be locked in place to ensure that no changes are made, even though members could easily enter their information under each column.

Excel Classes Will Hone Your Column Management Skills

Locking and unlocking columns are pretty important features if you have any columns that do not regularly need to be updated. Our Excel classes can teach you the finer points of these concepts if the above Excel tutorial is not descriptive enough for you.

Why is the VLOOKUP Function in Excel So Important?

This article will give you a solid introduction to the VLOOKUP function in Excel, but the first question to ask is “Why do we need to look up things?” You’ll find yourself looking up things a lot in your Excel classes, as well as during your practical applications of Excel in your daily life.

Well, when I was a kid, I loved finding things out. I wanted to be the smartass know-it-all!

I was obsessed with dinosaurs, aviation and most importantly professional wrestling. I would spend countless hours reading books on those topics. I have a very logical brain and I would love listing these things so that they were organised enough for me to later memorise them (did I mention I was a geek?!).

Then like a true dino-geek (that’s what they call us!), I would list them out like this…

Dinosaur	Origin	Length	Weight	Diet
Anklyosauras	Plains	12m	7 Tonnes	Herbivore
Allosaurus	Woodlands	7m	2 Tonnes	Carnivore
Brachiosaurus	Woodlands	30m	80 Tonnes	Herbivore
Gigatosaurus	Swamps	12m	8 Tonnes	Carnivore
…	…	…	…	…

That way, I had a table of information I could use to easily find information associated with each dinosaur.

Discovering associated information is at the heart of finding or looking up. This Excel class will teach you the virtues and applications of having this information.

How Is This and The VLOOKUP Function in Excel Related to Modern Day Business?

In everyday life we need to find information. It could be searching for a phone number by looking up a name in your contacts list on your address book on your mobile device, or it could be the price of a menu item in a restaurant or online. Excel classes will teach you how to look up all of the relevant information that you need to access.

In summary, both these processes involve looking at a name or reference and then finding its associated value. That could be a telephone number, cost or anything else associated with that record. Our online Excel courses will emphasize the importance of being able to locate this important information, as well as how to go about doing exactly that.

This same principle applies to the business environment.  Let’s say you are a management accountant working with human resource data to build the annual budget.

Now, you have a list of employees and their associated information such as salary for different resource types (i.e. analysts, project managers, developers, infrastructure designers, etc.).

You know that you need to plan for 10 analysts, 3 project managers, etc., so you need to start finding important data about these resources such as salary to start shaping a budget.

Perhaps your work involves selling products or services, these would generally have a product ID.

Let’s say a potential customer needs more information about these products, you can find that information with the product name or a reference number and once you know the reference number you have access to anything from product name to its weight to materials.

How to Use the VLOOKUP in Excel to Find the Information We Need

Often at work, you are in situations where you need to find out some information from a record.  Perhaps, as above you need to find the selling prices (for example) of a selected number of products by using the product ID’s and looking through a huge master list numbering thousands of rows of data.

You could look each product ID up manually and check the price, then enter it into your report. This is the lengthy option and often fraught with the risk of human error.

Thankfully Excel is a great tool to make jobs like this easier.  If you’ve never heard of the VLOOKUP function, then you are about to be initiated into the foundation of any Excel user’s skill-set.  I have found over the years that is the number 1 formula Excel has to offer (joint #1 with if).

It’s definitely one of the most useful functions (my favourite formula along with if!) and you will find that the VLOOKUP function will make many finding information effortless, efficient, and more accurate. Now I will pass this information along to you in this Excel tutorial.

If you’ve never heard of or used either, then I suggest get some Excel training as soon as possible.

What Exactly Is A VLOOKUP Function in Excel?

In summary, the VLOOKUP function in Excel is able to use a given reference and look for it in a specified range and then return a value from the same row in another column.  You can instruct the function to find an exact match or the closest match.  The former is best used for ID’s when you need to find a person’s employee number or a product ID for example. The latter is useful for finding a graded result if the lookup criteria sits in between a bracketed range, more on this later.

The function broken down looks like the below, in summary we will ask:

Find my reference in a range, when you find it, please tell me the piece of information that is x amount of columns to the right of that data.

How Do I Use the VLOOKUP Function in Excel?

Before I look at how to use this valuable function in Excel, let’s first take a look at it in the form as found in Excel.

=VLOOKUP(lookup_value, table_array, col_index_number, [range index number])

Breaking down each element, Excel defines:

lookup_value as the value to be found in the first column of the table. This can be a value, a reference, or a text string.  This is your employee or product ID I mentioned above

table_array as a table of text, numbers, or logical values, in which data is retrieved.  Table_array can be a reference to a range or a range name.

col_index_number is the column number in a table_array from which the matching value should be returned.  The first column values in the table in column 1.

[range index number] is a logical value: to find the closet match in the first column (sorted in ascending order) = true or omitted; find an exact match = false.  Additional note: You can also use a zero 0 (for false) or a one 1 (for true).

What Is A Good Excel VLOOKUP Example?

Using the product ID example above let’s look at how we can easily extract the price from a master list by using the product ID as the lookup reference.

In the example above, our lookup_value is shown in cell G3 in blue. We’ve used a cell reference to indicate this, but you could enter “D40” in quotation marks as it’s text.

Our table_array is highlighted in red.  Our lookup_value must appear in the first column of the table_array, we can see it does in cell B7.

Next, we’ve told the function to look at column 3 of the table_array. In this case that is column D. Note that this is not column 3 of the sheet i.e. column C, it’s the third column within the range you have selected.

Finally, we’ve used the false or 0 indicator to let the function know we want an exact match; 99% of the time you want exact matches. I will discuss the 1% later, as it is very useful when needed.

All of that combined means the function looks for the text string “D40” in the range highlighted in red above, finds it, then looks into column 3 of that range and returns the value shown on the same row.  In this case it £2.25 as shown in the result cell G5.  We can manually trace across from product ID “D40” and count across 3 (including the starting cell), to find we stop in cell D7, there we can see our result.

Why Must You Store Your Data Vertically for VLOOKUPS Functions in Excel?

In order for a VLOOKUP to function, it’s important to follow many practices. One of those is, as the title suggests, is to store your data vertically. This is another concept that is taught in any quality Excel class.  We can use the same example above to highlight this. One key thing to note is the V in VLOOKUP, stands for vertical, so vertical look up, that is literally what the function does, it looks up the criteria you want vertically.

As you can see below we have the master list. This could be a sheet name. It’s generally good practice to ensure your raw data is formatted in this tidy manner, with one column for each set of data.  This will make using function of any type easier and make your workbooks looks clear and professional.

This below, is better than…

… this!  The data is split over more columns than is needed, plus some of the data is in the wrong columns and column heading is missing.  A tidy, well labelled worksheet is a friend to you and those others that may need to use it.  A spreadsheet should be able to tell a story and give a clear indication of what the data is.

VLOOKUP is not suited to data stored in this horizontal format.  If we were to try to use VLOOKUP on the below it would expect to find all the lookup criteria for a category in the same column, there are however, none, as these are all stored horizontally.

I hope you have found the article useful and already realised where you can use it in your daily work.

As I mentioned before, the VLOOKUP function is one of the most useful functions, if you don’t know it, you need to and if you are reading this, you should be able to start harnessing some of it’s amazing potential.

Excel Classes: Keeping Your Information Organized and Accessible

Sure, you need to keep your data organized and a spreadsheet is one of the best ways to do this. But you also need to be able to easily access the data that you need for any given situation in your professional life. Our Excel classes will not only introduce to you the VLOOKUP function, but also make you aware of the finer points of its use and practical applications.

What if I Need to A Remove a Password from an Excel Workbook?

Workbooks or worksheets can be protected to prevent the manipulation of data either intentionally or accidentally. Excel classes can help you learn how to protect your documents from this kind of ruin, but this Excel tutorial can offer the same information. Passwords can be put in place to ensure only those employees who have the password can access the workbook or worksheet.

The following article will detail how to remove password from Excel that is password protected, unprotect a workbook that is password protected, and how to save the information in a password protected workbook when the password is forgotten or lost.

But first understand that a protected worksheet limits the users’ ability to edit within a single worksheet and a protected workbook prevents users from making structural changes like moving, deleting or adding sheets to the workbook.

Neither of these protections will prevent someone from opening the Excel workbook. They only limit the freedom within the workbook or worksheet. That being said, protecting a workbook or worksheet should be used when you want to prevent the tampering of your data or structure of your workbook. However, this should not be used as a security measure to protect sensitive data.

Want to Learn How to Remove Password from Excel Worksheet?

A protected worksheet limits the ability to edit, as this Excel course will illustrate.

Go to the review tab in the ribbon and select “unprotect sheet” or “unprotect workbook.” You will now be free to make changes within the worksheet. Just remember to protect it again once all work has been completed. My Excel classes will teach you the importance of remembering this final step before exiting your workbook.

How to Remove A Password from Excel

If the worksheet or workbook is password protected, when you click on unprotect sheet or unprotect workbook a dialogue box will open asking you to enter the password. Once the password has been entered the worksheet or workbook will be unprotected and you will once again be able to make changes or edit.

It is literally that easy to know how to remove a password from Excel. You don’t need to take a beginner course in Excel to understand this, but if you struggle with the finger points, some additional Excel training could be helpful.

How Can I Save The Data from A Password Protected Worksheet or Workbook?

If you have forgotten the password to a workbook or worksheet there is an easy way to retrieve your information and once again be able to edit or make changes.

1. Select all the data within the first worksheet by clicking the select all button in the top left corner (highlighted).
2. Control + C to copy or right-click and select copy.
3. Open a new Excel workbook (control + N).
4. Click anywhere within the new workbook and control + V to paste or right-click and select paste special (to ensure any custom formatting is withheld).
5. Continue this process with each worksheet within the workbook.
6. This method will only work if when protecting the worksheet, the boxes remained checked to allow users to select locked cells and select unlocked cells.

Are There Other Ways to Unprotect Excel Worksheets and Workbooks?

Above are the easiest ways to unprotect Excel worksheets and workbooks, but there are several other ways to unprotect them. We will detail them in this Excel tutorial.

You can right click on the worksheet tab and select unprotect sheet.

From the home tab in the ribbon select, click format and select unprotect sheet.

Click the file tab in the ribbon. Or from the home screen, you can also click unprotect to unprotect the workbook or the worksheet. Now you’ve learnt how to remove password from Excel, you can try some of our other Excel courses and further expand your Excel skills.

Excel Classes Will Help You Keep Your Data Safe

Though this is a fairly simple, beginner Excel class lesson, it doesn’t hurt to brush up on your information. When you are managing private or sensitive information, there is never any harm in adding extra protection to your documents. My Excel classes will help you to effectively protect the workbooks and spreadsheets that you don’t want interfered with.

How do I Separate First and Last Names in Excel

You will often receive spreadsheets containing usernames. Sometimes these spreadsheets have the first name and the last name in different columns. However, spreadsheets usually contain the full name in a single column. Excel classes can teach you the answer to the question of: “So, what do you do then?” Simply follow the options mentioned below to separate first and last name in Excel into two columns.

How Can You Separate First and Last Name Through The GUI?

Excel’s parsing concept helps you to split and spread text from one column to multiple columns. We can parse full name into first name and last name using Excel’s text to columns wizard. Follow the steps mentioned below in this Excel tutorial to understand how text to columns works:

Step 1: Select the column that contains the full name. Instead of selecting an entire column you can also select a range, but make sure that you are not selecting text from multiple columns. Additionally, ensure that there are empty columns to the right of a selected range or column. This is an important step as text on adjacent columns will be overwritten if there are no empty columns.

Step 2: On your Excel ribbon, select data and then click the option “text to columns”. You will now see the convert text to columns wizard.

Step 3: The first step in this wizard is to select the data type. You have two options. First is the delimited and the second is fixed width. Select delimited if your data is separated by characters such as commas, tabs, any other special characters or symbols. Select the option “fixed width” if the data in your column is separated by fixed width spaces.

Step 4: If you have chosen delimited, then you would be able to choose the delimiters in the next screen. Available options are as follows:
1) Tab

2) Semicolon

3) Comma

4) Space

5) Other

If you select option 5 ‘other’ it would allow you to type a character in a text box.

If you have chosen the option fixed width rather than delimiter, then you would be able to set column breaks. To insert columns breaks in the data preview window, click on the desired position. To delete a column break, double-click on it. To move a column break, you can click and drag it to the new position.

Step 5: The third screen would allow you to set the data type for the new column that would be populated with the data and is created at the end of this wizard. The default option is general which converts numeric values to numbers, date values to dates, and all remaining values to text. Other available options are text and date. You also have an option that does not import the data to a column.

Step 6: Press finish and you will see the ability to see the First Name and the Last Name in new columns.

Can You Separate First and Last Name Using Formulas?

There are no direct and specific formulas to separate first and last name in Excel. However, my Excel classes can teach you the art of combining multiple Excel formulas we can achieve this.

How Can I Get the First Name in Excel?

Using left and search formulas offered to you in this online Excel course, you can build a formula to extract the first name. Below is the syntax.

Syntax:

=LEFT(CELL ADDRESS, SEARCH(“ “, CELL ADDRESS) – 1)

Do replace the cell address in the above syntax with the actual value as shown in the example below:

=LEFT(B2,SEARCH(” “,B2,1)-1)

According to the example in this Excel training tutorial, the full name is present in the Cell B2. When we type this formula in the cell C2, it would generate the first name.

To get the last name:

Using right, len, search formulas you can build a formula to extract the last name. Below is the syntax

Syntax :

=RIGHT(CELL ADDRESS,LEN(ADDRESS) -SEARCH(” “,CELL ADDRESS))

Do replace the cell address in the above syntax with the actual value as shown in the example below:

=RIGHT(B2,LEN(B2) -SEARCH(” “,B2))

According to this example, the full name is present in the cell B2. When we type this formula in the cell D2, it would generate the Last name.

How Does it Work?

The formula searches for spaces or commas in the cell’s text. If a space or comma is found, all the text found on its left is extracted and displayed as the first name. The last name is displayed by extracting all text to the right of the space or comma.

Can I Separate First and Last Name in Excel Using A Macro?

Using a macro, you can create user defined functions. You can then use these functions in your Excel sheets. This will help you attain your goal using a single function. This is a more challenging option, so you might need additional Excel training to use this method.

The cell containing the full name should be passed as the parameter to these user defined functions.

Function GetLastname(v_cell As Range) Dim v_firstname As String GetLastname = VBA.Right(v_cell, VBA.Len(v_cell) – VBA.InStr(v_cell, ” “)) End Function

Function GetFirstname(v_cell As Range) Dim v_firstname As String GetFirstname = VBA.Left(v_cell, VBA.InStr(v_cell, ” “) – 1) End Function

After posting these scripts into a new module, you can use these functions as shown in the images below:

This will now have allowed you to separate first and last name in Excel.

How Excel Classes Can Make Your Use of Excel Easier

Imagine how much of a pain it would be to organize a list for the same purpose but doing so manually instead. Yeah, that would take a long time! Fortunately, my Excel classes are here to help you figure out the easiest manner of accomplishing this task.

Using the COUNTIF function in Excel: Beginner to Advanced

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

=COUNTIF(range, condition)

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 

=COUNTIF(B2:B9,”Renovation”)

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,

=COUNTIF(A2:C9,”>10000”)

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:​

=COUNTIF(A2:C9,">=10000") - COUNTIF(A2:C9,">20000")

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 

=COUNTIF(A2:A11,"<>"&A3)

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:

=COUNTIF(B2:B11,"Reno*")

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:

=COUNTIF(A2:A11,”????ON”)

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:

=COUNTIF(D2:D11,D2) + COUNTIF(D2:D11,D10) + COUNTIF(D2:D11,D3)

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:

=COUNTIF(C2:C11,">=1/1/17") - COUNTIF(C2:C11,"<3/31/17")

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:

=COUNTIF(A2:E11,"<>"&"*")

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:

=COUNTIF(C2:C12,C2)>1

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:

=COUNTIF(E2:E12,”TRUE”).

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:

=SUMPRODUCT((COUNTIF(A2:A12,B2:B12)>0)*(B2:B12<>""))

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:

=SUMPRODUCT((COUNTIF(A2:E2,A2:E2)>1)*(A2:E2<>""))

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.