Essential Excel Formulas and Functions

From https://medium.com/@seekernaukri80/what-is-basic-formula-of-excel-6e76ca90113c

Excel is a powerful tool that allows users to perform a variety of tasks, from simple calculations to complex data analysis. Whether you’re a beginner or an experienced user, knowing the essential Excel formulas and functions can greatly enhance your efficiency and productivity. In this article, we’ll cover some of the most common functions, including SUM, SUMIF, AVERAGE, and different types of COUNT functions.

Common Functions You Need to Know

Basic Arithmetic operations

Addition

1. If you want to add two numbers in excel your going to have to use a formula. To start , select a cell. After we select a cell type “=” all formulas start with that. Then proceed to the select the first cell you want to add then use the “+” operator then select the second cell. Finally press and enter and their you go!
2. One thing to note is that selecting a cell will cause the formula to dynamically update. Meaning if I change a value from that cell I selected later on then the output of the formula will change.
3. Lets give an example , if we select a cell to write our formula in it would look something like this “=A3+A4”. Pretty easy right? But what happens if we wanted to add 100’s of numbers that would get pretty tedious. We will later learn how to do that in a much more simpler and efficient way!

Other Operations

1. If you wanted to multiply two numbers it would be same idea replace the “+” operator with “*” operator and the same goes for subtraction (“-”) and division (“/”).
2. Here’s an example for each “=A2-A1” ( Subtraction), “=A2*A1” (Multiplication), “=A2/A1” (Division).

Using Parenthesis

1. Just like in your conventional mathematics. Wrapping a expression in parenthesis will give the expression priority.
2. Hers an example. Lets say we had this long expression “=(A1+A2)*A3”. What will happen here is that A2 will be added first to A1 since we wrapped in parenthesis. Then the total will be multiplied by A3 giving us our final result. so “=(5+3)*8” would give us 64! If we didn't wrap it in parenthesis , multiplication would have been given the next priority over addition so then the result would have been 29 since 8*3 is 24 and plus 5 is 29.

Using Basic Arithmetic operations ( Click to enlarge GIF )

SUM and SUMIF Functions

SUM Function

1. As I mentioned before imagine you had to sum up a list of 100 numbers and having to input the formula like this “=A1+A3+A4…” and so on. Not only would you be here forever, you would also increase the chances of making a mistake. Luckily we have a function that can speed up this whole processes and its called the SUM() function.
2. Its usage is pretty simple , SUM(sum_range).
3. Lets say we were trying to up the values from cells A1 to A100. We can do many things to get the range for our function. For starters we could do this “=SUM(A1:A100)” by adding a colon we specify that this is the range we want. The colon can also be automatically added if you select your starting cell and drag all the way down to your desired destination. Simple right?

SUMIF Function

1. The SUMIF() function takes our SUM() function one step further. Lets say you wanted to sum up values that met a certain condition. Like finding out how much we spent on each category from food to entertainment. That's when the SUMIF() function shines. Lets take a look at the syntax.
2. The syntax of the SUMIF(range, criteria, [sum_range]). The range being the range of cells you want to be evaluated by the criteria like the column of cells that include category for spending. Then the criteria which would be for example what specially your trying to sum up like “food”. Finally the “sum_range” which is the actual values that will be summed up after we have filtered our list of values based on the criteria.
3. For example lets say we wanted to sum up the money spent on row A and a specific criteria that is located in row B this is how it would look like. SUMIF(B1:B10, “food”, A1:A10).

Using SUM and SUMIF Function ( Click to enlarge GIF)

AVERAGE Function

1. The AVERAGE() function is exactly what it sounds like. It takes the average given by a specific range of cells that are numbers or at the very least and be translated into numbers. Instead of you just adding up the cells or even using the SUM () function and the dividing it by the total. You can use this single function. Lets check out how its written.
2. The AVERAGE() function syntax is “=AVERAGE(number1)”. number1 represents the number or range you want to average. Pretty straight forward! Now lets look at an example
3. Lets say we have a list of employees and we want to find the average for John during all the quarters of revenue last year. We can go to our desired output cell and assuming the the four quarters are from cell B to E then we write as so “=AVERAGE(B:E)” and bam we done.
4. If wanted to do it for the other 100 employees fear not. We can actually take the formula we written for john and drag the cell down to other cells to apply that same formula!

Using the AVERAGE Function ( Click to enlarge GIF)

Different Types of COUNT Functions

COUNT

1. COUNT() function is pretty straightforward. It only counts the cells with only numbers in it.
2. The Syntax is written like this “=COUNT(range)”. Again only working with numbers all you have to do is specify a range.
3. Here’s an example “=COUNT(A1:A10)” assuming only 8 numbers are included in these cells you will get the count which should be 8.

COUNTA

1. Lets say you don’t just want to count numbers but text as well. The COUNTA() function does exactly just that. Counting both numbers and texts but not anything blank however.
2. The syntax is written like this “=COUNTA(range)” . Again same idea but now it counts text as well as numbers but not blanks
3. Here’s an example “=COUNTA(A1:A10)”. Assuming their are 8 numbers 1 text and one blank the answer should be 9. Since we are counting the 8 numbers + 1 text but we don’t count the blanks.

COUNTBLANK

1. Lets say we want to only count how many cells are empty this time, no number , no texts, nothing. The COUNTBLANK() function will do just that!
2. The syntax is as always “=COUNTBLANK(range)”. Again same idea but this time it will only count empty cells.
3. Lets say we wanted to find out if their are any missing values quickly in a certain column. We could do “=COUNTBLANK(column_range)” and if their are any empty cells it would return how many are empty.

COUNTIF

1. If only their was a way we could count the cells that meet a certain condition just like our SUMIF() function from earlier when it comes to summing up numbers. Well their is! Ladies and Gents I give you the COUNTIF function. By specifying the criteria of what we want to be counted or not counted we can get what we are looking for. Lets take a look at the syntax.
2. The syntax is written as follows “COUNTIF(range,criteria)”. That's it! Only taking the range of the criteria your looking for. Lets take a look at an example.
3. Lets say we wanted to find out how many times we ordered food in our category section of spending. Lets assume that the category is located in column B and is 100 cells long. The formula would be written as follows “COUNTIF(B1:B100, “food”)”. Their we have it!

COUNTIFS

1. Now that we have learned how to count using one criteria. What happens when we want to count using multiple criteria's? We can use the COUNTIFS function which only counts the cells that meet all the conditions that were specified. Lets see the syntax
2. The syntax is written as follows “COUNTIFS(range1,criteria1,range2,criteria2)” and so on. As you can see this enables us to add more criteria to our original COUNTIF function. Allowing for more complex filtering of our data.
3. Lets take a look at an example. Last time we only counted the food in our category column but lets say now we want to count not only food but food that was also purchased with a credit card. Assuming our payment method is in column D and category again it column B ,lets see how we do that. First we write our function “COUNTIFS(B1:B100, “food”, D1:D100, “credit card”)” and their you go it will only count the cells with both food and credit card in them. Now lets go deeper into these functions and see what their really capable of.

Using COUNT Functions ( Click to enlarge GIF)

COUNTIF and COUNTIFS in a Deeper Level

Using COUNTIF for Specific Conditions

1. Lets say we wanted to know how many times the word “red” appears in the column of a specific cell we could do “COUNTIF(range, “red”)”. Lets also say we want to count two different values in the same exact column we can also combine formulas to create more complex conditions as so. If we want to see how many reds and yellows their are we would write it like this “COUNTIF(range,Yellow) + COUNTIF(range, “red”)”.
2. We can also use conditions in our COUNTIF function. If we wanted to count how many numbers in a column are less than 10 for example , “COUNTIF(range, “<10”). We can also take it one step further and create a range that we want to count by combining formulas like so, “COUNTIF(range, “<10”) — COUNTIF(range, “<1”)”. By doing this we get the count of numbers less than 10 but then we take the numbers less than 1 and subtract it to only get the count of the numbers between 1–10 exclusive.

Using Wildcards in COUNTIF

1. We can also use wildcard character “*” to see if a value ends or starts with a letter. The proceed to count how many that do.
2. Here’s an example. Lets say we wanted to count how many of the cells start with the letter “j” we write as follows “COUNTIF(range, “j*”)” or how many 4 letter cells end with r , “COUNTIF(range, “???r”)”. The question marks are serving as placeholders to say any value that's this long but ends with r.

Advanced COUNTIFS Examples

1. We can take this one step further by using COUNTIFS function to help us analyze more complex situations that require us to filter through more than one criteria as mentioned before
2. Lets say we are looking at table of sales data and we want to count the amount of east regions that had more than 400 orders. If you noticed we need to find two things. First being the region and second the amount of orders. This signals we need to use the COUNTIFS function like so, “COUNTIFS(range1, “east”,range2, “≥400”)” thus only counting regions that are east with more than 400 orders.

Advanced COUNTIF and COUNTIFS Usage ( Click to enlarge GIF)

Overall Functions are powerful tool provided to you to make quick work of tasks that would otherwise be too time consuming and mind numbing. By learning how to utilize tools like SUMIF and COUNTIF for example you can take your data analysis one step further and delve into deeper insights. In the next article we will cover filtering and sorting to take your analysis to the next level. As always feel free to comment below with any questions or mistakes I may have made. Other than that, good morning , good evening and good night.