Excel formulas that make you smart at work

If you are one of those who use Microsoft Excel at workplace, you must be using some of the common functions like SUM, COUNT, AVERAGE, etc. Excel Formulas make our work easier and save a lot of time. But many times, you do things manually because you don’t know what formula to use and which excel function can help. Actually there are hundreds of functions in Excel and each one of these is meant to do some specific task. Knowing about all these will be quite difficult for an average excel user and no one actually recommend you to learn everything. But there are handful of excel formulas which are extremely useful and can make you really smart at work!

In this article, I am going to list down the top 10 excel function sets which you must know if your work demands working with Microsoft excel.

COUNT & COUNTA

As the names suggest, COUNT and COUNTA functions are used to count the number of cells which are non-empty. While COUNT function will only count the cells with numbers (and dates), COUNTA will count all cells which are non-blank (including text).

Syntax:

=COUNT(value1, value2, value3,…..)

=COUNTA(value1, value2, value3,…..)

Example

Excel Count & Counta formula

Using COUNT & COUNTA formula in excel

For more information, have a look at COUNT & COUNTA functions article.

IF & IFERROR

IF function is the logical function in excel which returns a value based on some condition. This is one of the most important functions to learn because you will use it quite extensively.

Syntax:

=IF(logical_test,value_if_true,value_if_false)

Let’s understand IF function with an example.

You are required to pay 20% income tax if your total income is equal to or more than $50000. However, for senior citizen (with age more than 60 years), it is only 15%. If your income is less than $50000, you don’t require paying any tax.

Excel IF Formula

Excel IF formula Example

In above formula, we are using nested IF function. The first IF statement checks if the income is less than $50000. If true, it returns zero. Otherwise, it uses second IF statement to check the age. If it is more than 60, it returns 15% of the total income otherwise 20% of total income.

You can learn more about Excel IF function here.

IFERROR has been added with Excel 2007. You can use to it to display a value when an error is found in a formula. Handy when you don’t want to scare your visitor with an error message like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Example

excel-iferror-formula

Using IFERROR formula for customer error message

COUNTIF, SUMIF and AVERAGEIF

Sometimes you need to count the number of cells based on some condition. For example from a sales data table, you just want to count the number of sales with a value more than $10; COUNTIF function can be quite handy. Similar to COUNTIF, there are two more functions SUMIF and AVERAGEIF which do the SUM and AVERAGE based on criteria.

Syntax:

=COUNTIF(range, criteria)

Excel COUNTIF Formula

Using COUNTIF formula for comparing numbers

You can go through my tutorial on SUMIF function for more details.

SUMIFS

While COUNTIF, SUMIF and AVERAGEIF allow you to specify one condition, there is another set of functions which will let you use multiple criteria at the same time. These are SUMIFS, COUNTIFS and AVERAGEIFS and have been introduced with Excel 2007.

Syntax:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Here, you first need to define the sum range followed by criteria range and criteria.

Suppose, from a sales table, you wish to know the SUM of sales value of any specific brand sold by some specific sales person with a specified value, SUMIFS can be quite handy. See the example below:

Excel SUMIFS formula example

SUMIFS formula to define several criteria

There are three criteria in this example. And the formula is:

=SUMIFS(Table1[Sales Value],Table1[Model],”Samsung”,Table1[Sales Person],”Jullie”,Table1[Sales Value],”>250″)

VLOOKUP

One of the most powerful function in excel is VLOOKUP. Learn it today and you will be much ahead of your colleagues which don’t know how to use it.

VLOOKUP formula searches a value from any column of a data table and returns any value from the searched row. For example if you have list of few people and you wish to get phone numbers and address from a huge database, VLOOKUP will be quite handy. You can also use VLOOKUP to compare two data tables and find out the common and odd values.

VLOOKUP is a big chapter and I won’t explain everything here. For understanding it once for all, go through my complete guide on Excel VLOOKUP.

CONCATENATE

These two functions are string functions of excel. CONCATENATE will combine the values in two (or more) cells into one. For example, if you wish to get the full name of a person from a data table with first name, middle name and last name; you can easily get it with CONCATENATE function.

Syntax:

=CONCATENATE(text1, text2, text3,….)

Though, if you don’t want to remember it, you can simply use “&” to do the same task like this:

= text1 & text2 & text3 & ……

Example

Excel CONCATENATE formula

CONCATENATE formula for combining text

Note that to insert space between first, middle and last name we have also combined spaces.

TRIM & LEN

TRIM function can save you from headache by removing the extra spaces in a cell. It will leave only a single space between two words and remove any other space.

Sometimes, when there are extra spaces in a cell before or after text (it happens especially when you pull the data from database), many of important functions like VLOOKUP and IF will not work properly. In that situation, you can first use TRIM function.

Syntax:

TRIM(text)

In this example, I have added a space before the text and extra space in between two words. See, what trim does.

Excel TRIM formula

TRIM formula to remove extra spaces

In the above example, we have used LEN function to count the number of characters. No need to mention here that it also includes the spaces.

Syntax:

=LEN(text)

UPPER, LOWER & PROPER

These three functions are string functions and are used to transform the text to upper, lower and proper cases. Don’t worry if your colleague has typed everything in Upper case and you want to make it lower or create proper cases for the texts filled in hundreds of cells. Proper cell will capitalize the first letter in the text string and also any other letter which is not after a letter.

Syntax:

=UPPER(text), =LOWER(text), = PROPER(text)

Example

Excel Case conversion formulas

Using UPPER, LOWER and PROPER formulas

LEFT & RIGHT

LEFT and RIGHT are the string manipulation function and are quite handy in a lot of situations. Suppose from chassis numbers, we wish to know the year of manufacturing which is coded at the fourth and fifth letters. We can easily get that using LEFT and RIGHT functions together.

LEFT is used to get the number of characters from the left. Similarly, RIGHT gives the number of characters from right.

Syntax:

=LEFT(text, num_char), =RIGHT(text, num_char)

Here the num_char is the number of characters from LEFT and RIGHT you wish to return.

Example

Excel LEFT and RIGHT Formulas

Using LEFT and RIGHT formulas to get the manuf. month

In the above example, we are first extracting five characters from left and then out of this, two characters from right. This gives us the month of manufacturing of the chassis.

DATE Functions

The last one in this list is the DATE function. You provide the year, month and day and it returns the date. This is quite simple but very powerful when you wish to do some date manipulation like addition or subtraction of day, month or year.

Syntax:

=DATE(year,month,day)

Similarly, if you wish to extract the year, month or day from any date, there are three functions YEAR, MONTH and DAY.

Syntax:

=YEAR(date), MONTH(date), DAY(date)

Suppose we are interested to get a date exactly after 100 days from now, we can use the following formula.

Excel DATE formulas

Date manipulation with excel DATE functions

In the above example, we are first using TODAY() function which returns the current date. After that, we are first extracting year, month and day and adding 100 in day. Then we are using DATE function to get the date after 100 days.

Summary

The above 10 sets of Excel Formulas are extremely useful at simplifying your work. You can combine two or more functions together to make a more powerful formula. I frequently use these functions for my work and am sure if you know about these, you can work really smarter!

Excel COUNT, COUNTA & COUNTBLANK Functions

One of the common tasks associated with data is to do counting. MS excel comes with a number of built-in functions which can do this task for you exactly as per your requirement. Normally, you want to count the number of cells with data or based on some condition(s). In this article, we will learn about basic Excel Count Functions and how to effectively select and use the more appropriate one. So what is the difference among COUNT, COUNTA and COUNTBLANK functions and how to use these?

In fact, if you just need to count the number of cells with data, you don’t need to use any formula. Just select the cells and average, count and sum are automatically displayed in the status bar if you are using Excel 2007 or above. For Excel 2003 or earlier, you can right click on the status bar and select any one of average, count or sum.

Count Function

Unlike the name suggests; Count Function does not count the number of cells with data. It counts the number of cells which contain numbers. It will ignore any text, reference, blank cell, blank text etc. and will return the count of cells with numbers only. Next time when you are using COUNT formula, be careful!

COUNT Formula

The syntax for count formula is really simple.

COUNT(value1, value2, value3, …)

Here value1, value2, etc. are arguments and can be either values or cell references.

Suppose we need to count the cells with numbers in cells A1 to A10 in cell C3.

Count Function in Excel

Using COUNT Function

  1. In cell C3, write “=COUNT(“ (without parenthesis)
  2. Now select the cells from A1 to A10
  3. Use “)” to close the formula which becomes “=COUNT(A1:A10)
  4. Press ENTER to get the result.

If we want to count the cells with numbers in Cells A1 to A10 and B2 to B15, the formula will be

=COUNT(A1:A10, B2:B15)

What if the cell contains a DATE? In excel, it will be considered as number and will be taken into account. Try to insert a date in a blank cell to see the result.

Counting a cell with date with count function

Count Function will count a cell with Date

COUNTA Function

What if you want to get the count of cells with any type of data (non-empty) which you see in status bar? Well, here we use COUNTA function. COUNTA function will count any cell which is non blank.

Syntax of COUNTA Function

The syntax of COUNTA formula is similar to that of COUNT formula.

COUNTA(value1, value2, value3, …)

Here also the arguments can be either the values or reference of cells.

Let’s use the same data table to see the result with COUNTA function.

=COUNTA(A1:A10) returns 7 counting all non-empty cells.

Excel COUNTA Function

COUNTA function to count the number of non-blank cells

COUNTA function will also count the cells with Error value (like #N/A).

COUNTBLANK Function

Excel COUNTBLANK function is opposite to COUNTA function. It counts the number of empty cells in the array or range.

Syntax of COUNTBLANK

COUNTBLANK(range)

Let’s count the number of blank cells in the same example.

COUNTBLANK(A1:A10)

COUNTBLANK Function in Excel

Counting the blank Cells

Note however that unlike the syntax of COUNT and COUNTA functions, COUNTBLANK takes only one argument. So, COUNTBLANK(A1:A10, B2:B15) will not work!

Conclusion

These are three basic count functions in excel which helps you count the number of cells with numbers, any data and blank cells. For counting number of cells with condition, we can use COUNTIF or COUNTIFs functions.

VLOOKUP in Excel – Problems and Troubleshootings

Excel VLOOKUP function is used to lookout and retrieve the information from a data table. However, sometimes you may find that even if you have done everything right, VLOOKUP does not work. There are a few common problems due to which it will return #N/A error. I am listing down the common problems and their troubleshooting. Try the following solutions and I am sure, it should start returning you the correct value.

Formula not correct

Believe it or not, in most of the cases, the main problem is the incorrect formula. A simple mistake can prevent the formula to fetch the correct result. So, make sure that you have written the correct syntax – see the comma, spelling mistakes, brackets, extra spaces, etc. first.

VLOOKUP Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Just make sure there is no additional space, you have used comma at appropriate place and most importantly not missed the last optional range_lookup value. In most of the cases you will pass the value “FALSE” for it. Another thing to keep in mind is that the lookup_value must be in the first column of table_array. Use insert function dialog box instead of writing to code manually to avoid typing mistakes.

Use of Relative Reference

In practical scenario, most of the times you will write the formula in one cell only and then drag it to the other cells. If you are not using absolute reference, the table_array will get changed along with lookup value as you drag it. But for most practical purposes, your table_array should be same. This will lead to getting #N/A error in many of your cells. Let’ understand it with this example.

VLOOKUP absolute Reference Example

Here, we want to get the cost of Pant, Belt and T-Shirt from the table_array (A2:B7). For cell E2, we write the following VLOOKUP Formula.=VLOOKUP(D2,A2:B7,2,FALSE)

Now, for cells E3 and E4 where we want to get the cost of Belt and T-Shirt, we simply drag to copy the formula. But as you can see, we are getting the “#N/A” error in cell E4.

This is because, once you drag to copy the formula, the table_array also gets changed. Look at the formula in E4 cell.

Relative Reference in VLOOKUP

Problem of Relative Reference in VLOOKUP

=VLOOKUP(D4,A4:B9,2,FALSE)

It has changed from A2:B7 to A4:B9

To overcome this issue, you need to use the absolute reference. For doing so, select the table_array (A2:B7) in cell E2 and press F4. You will find “$” before A, 2, B and 9. It means that the range is now fixed and will not change when you drag the formula.

Absolute referencing with F4

Use F4 for absolute reference in VLOOKUP

Press F4 again and the “$” before A and B will disappear. It means, A and B (columns) will change but 2 and 9 (rows) will always be constant while you drag. If you press F4 once again, the $ sign before 2 and 9 will disappear and appear again for A and B. Now the rows can change but the columns are constant. Do some experiments and lookout in the formula after dragging to understand it once for all.

Format Change – Numbers stored as Text

This is another common problem. Many times, the lookup value which is a number is stored as text in the table_array. VLOOKUP is format sensitive, so you will get #N/A error.

See the following example:

Number stored as text in VLOOKUP

Use of Correct Format

Make sure that the format of lookup value and the value in the first column of table_array is same. To change the format of number stored as text to numbers, select all the affected cells, click on the warning icon before it and select convert to number. However, if you don’t want to change anything in your table_array, you can just modify the formula a little.

=VLOOKUP(TEXT(D2,”0″),A2:B5,2,FALSE)

Here we are first converting the number in D2 cell to text to match it with the table_array. Now since both the formats are same, you don’t get any error.

Lookup Value is not in the Left most column

As I mentioned it earlier, the lookup value must be in the first (left most) column of your table_array. If it is not the case, you may use INDEX-MATCH functions together. This is a good VLOOKUP alternative and you can go through my INDEX MATCH Functions tutorial.

Spaces before or after lookup value

There may be some spaces before or after lookup value. Solution is very simple – use TRIM() function to remove the extra spaces from both lookup value and first column of table array. How do you check the spaces? Select the cell with value and hit F2 button to inspect the value in formula bar.

Removing #N/A Error

In some cases you get genuine #N/A (value not available) errors. If you don’t want this error to appear and simply want a blank column instead, you can use the following trick to remove this.

=IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)=TRUE), “”, VLOOKUP(lookup_value,table_array,col_index_num,range_lookup))

Conclusion

These are some of the common reasons of Excel VLOOKUP formula not working. So, next time when you get stuck, check the above troubleshooting first. Even after the above solutions, if it does not work, let me know in the comments below- we will try to come out from it together.