countif Archives - HowtoExcel.net

15 Excel Functions Accountants Should Know

March 28, 2023

by howtoexcel accounting advanced average averages beginner count countif dates functions index intermediate lookup lookups match sum sumif sumifs unique vlookup

If you’re an accountant, you know that working with large amounts of data can be a daunting task. But with Excel, that work can get a whole lot easier and more efficient. Understanding Excel’s advanced features and functions can improve productivity, reduce errors, make your work more accurate, and most importantly — save you time. Below, I’ll go over some of the most important Excel functions that accountants should know, and provide examples of how to use them. For this example, I’ll use the following spreadsheet. Feel free to download it and follow along with the calculations.

1. SUM

The SUM function is a basic but essential function in Excel. It allows you to add up a range of values, which is helpful when calculating totals, such as revenue, expenses, and profits. Suppose you have a spreadsheet with sales data. In the above example, the total sales are in column G. If you wanted to sum up the entire column, the formula would be as follows: =SUM(G:G)

2. AVERAGE

The AVERAGE function calculates the average of a range of values. It is useful when analyzing data and preparing financial statements. In the above example, suppose you wanted to calculate what the average sale was. To do this, you can just use the AVERAGE function on column G, similar to the SUM function before. Here’s the formula: =AVERAGE(G:G)

3. IF

The IF function allows you to test a condition and return one value if the condition is true and another value if the condition is false. This can be useful because it can send your formulas to the next level. By knowing to use the IF function, you could also use SUMIF, AVERAGEIF, and many other functions that involve an if statement. In the above example, let’s say you only wanted to know if a value in cell M2 was part of the Motorcycles product line. The formula would be as follows: =IF(M2=”Motorcycles”,1,2). If it is part of Motorcycles, you would have a value of 1, otherwise, it would be 2.

4. SUMIF

By knowing the SUM and IF functions, you can combine them together with SUMIF, which is an incredibly popular function. It gives you a quick way to tally up the totals that meet a criteria. For example, let’s say you want all sales that relate to the Motorcycles category. The formula for that would be as follows: =SUMIF(M:M,”Motorcycles”,G:G). If the criteria is met in column M, then the formula will sum up the corresponding values in column G. There’s also the super-powered SUMIFS function, which allows you to combine multiple criteria.

5. EOMONTH

The EOMONTH function calculates the last day of the month for a specified number of months in the future or past. It is useful when working with data that is organized by date. For accountants, this can be useful when you’re calculating when something is due. Let’s say in this example, we need to calculate the date orders need to go out on, and that needs to be the end of the next month. Using the ORDERDATE field in column H, here’s how that calculation would look in the first cell, which would then be copied down for the rest: =EOMONTH(H2,1)

6. TODAY

The TODAY function is helpful for accountants in calculating deadlines and knowing how many days are remaining or past a certain date. Suppose that you wanted to know how many days have past since the ORDER DUE DATE that was calculated in the previous example. Rather than entering in a static date that every day you would need to change, you can just use the TODAY function. Here’s how a formula calculating the days since the deadline for the first cell would look like, assuming the due date is in column N: =TODAY()-N2. The next day you open up the workbook, the calculations will update to reflect the current date; there’s no need to make any changes. There are many more date calculations you can do in Excel.

7. FV

The FV function calculates the future value of an investment based on a fixed interest rate and a regular payment schedule. You can use it to calculate the future value of an investment or savings account. Let’s say that you wanted to save $10,000 per year and expect to earn a return of 5% per year on that investment. Using the FV calculation, you can do that with the following formula: =FV(0.05,5,-10000). If you don’t enter a negative for the payment amount, the formula will result in a negative value. You can also specify whether payments happen at the beginning of a period (1) or end (0 — this is the default) with the last argument in the function.

8. PV

The PV function lets you do the opposite and work backwards from a future value to the present. Knowing that the calculation in example 7 returns a value of $55,256.31, that can be used in the PV calculation to check our work: =PV(0.05,5,10000,-55256.31). The formula returns a value of 0, which is correct, as there was no starting value in the FV calculation.

9. PMT

The PMT function calculates the periodic payment required to pay off a loan with a fixed interest rate over a specified period. It is helpful when determining the monthly payments required to pay off a loan or mortgage. Let’s take the example of a mortgage payment where you need to pay down $500,000 over the period of 30 years, in monthly payments. At a 5% interest rate, here’s what the payment calculation would be: =PMT(0.05/12,12*30,-500000,0). Here again the ending value needs to be a negative to avoid a negative value in the result. And since the payments are monthly, the periods need to be multiplied by 12 and the interest rate is dividend by 12.

10. VLOOKUP

The VLOOKUP function allows you to search for a value in a table and return a corresponding value from another column in the same row. It’s one of the most common Excel functions because of how useful and easy to use it is. It is helpful when working with large data sets and performing data analysis. Let’s suppose in this example that you want to find the sales related to order number 10318. The formula for that calculation might look like this: =VLOOKUP(10318,C:G,5,FALSE). In a VLOOKUP function, you need to specify the column number you want to extract from, which is what the 5 represents. If you’re using Office 365, you can also use the newer, flashier XLOOKUP function. I put VLOOKUP on this list because it’ll work on older versions of Excel — XLOOKUP won’t.

11. INDEX

The INDEX function allows you to return a value from a data set by specifying the row and column number. It’s also helpful if you just want to return data from a single row or column. For example, the sales column is in column G. If I know the order number is on row 20 (which relates to order number 10318), this formula would do the same job as the VLOOKUP in the previous example: =INDEX(G:G,20,1).

12. MATCH

The MATCH function allows you to find the position of a value within a range of cells. Oftentimes, Excel users deploy a combination of INDEX and MATCH instead of VLOOKUP due to its limitation (e.g. VLOOKUP can’t extract values to the left of the lookup field). In the previous example, you had to specify the row belonging to the order number. But if you didn’t know it, you could use the MATCH function within the INDEX function. The MATCH function would look like this: =MATCH(10318,C:C,0). Placed within an INDEX function, it can replace the argument where in the previous example, we set a value of 20: =INDEX(G:G,MATCH(10318,C:C,0),1). By doing this, you have a more flexible version of the VLOOKUP function. You can also create dynamic formulas using INDEX and MATCH that use lookups for both the column and row.

13. COUNTIF

The COUNTIF function allows you to count the number of cells in a range that meet a specified condition. Let’s count the number of values in the data set that are Motorcycles. To do this, you would enter the following formula: =COUNTIF(M:M,”Motorcycles”).

14. COUNTA

The COUNTA function is similar to the previous function, except it only counts the number of non-empty cells. With no criteria, it is helpful to just the total number of values within a range. To calculate how many cells are in this data set, you can use the following formula: =COUNTA(C:C). If there are no gaps in data, then the result should be the same regardless of which column is used. And when combined with the UNIQUE function, you can have an easy way to count the number of unique values.

15. UNIQUE

The UNIQUE function returns a list of unique values within a range, and it’s a much easier method than the old-school way of extracting unique values. If you wanted to extract all the unique product lines in column M, you would enter the following formula: =UNIQUE(M:M). If, however, you just wanted to count the number of unique values, you could embed it within the COUNTA function as follows: =COUNTA(UNIQUE(M:M)). You can adjust your range if you don’t want to include the header.

This is just a sample of some of the useful Excel functions that accountants can utilize. If you are familiar with them, you’ll put yourself in a great position to improve the efficiency of your workflow and make your spreadsheets easier to use. Plus, you can confidently say that you are highly competent with Excel, which can make your resume more attractive and make you better suited for accounting jobs that require advanced Excel skills — and there are many of them that do!.

If you liked this post on 15 Excel Functions Accountants Should Know, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

How to Find Duplicates and Unique Values in Google Sheets

December 6, 2022

by howtoexcel conditional formatting countif formatting google sheets

Duplicate and unique values can be difficult to find in a large data set. In this post, I’ll show you how you can find and highlight duplicate values, as well as how to extract unique values, in Google Sheets. In this example, I’m going to use a list that shows historical World Cup results, including the winners of past years.

Highlighting and finding duplicate values

There is a built-in function in Google Sheets that allows you to filter out unique values. Under the Data menu, there is a section for Data cleanup where you can select the option to Remove duplicates.

Removing duplicate values in Google Sheets.

However, by doing this, you will actually remove duplicates. And if you don’t want to remove data, this could lead to unintended results. If you simply want to find and highlight duplicate values, you’re better off using conditional formatting.

In this data set, I’m going to highlight the duplicate values in the champion field to identify repeat winners. To do this, I can create a conditional formatting rule in Google Sheets to apply formatting when criteria is met. My criteria will be to look at whether a value shows up more than once within a list. The formula utilizes the COUNTIF function:

=COUNTIF(B:B,B1)>1

This formula needs to be added when creating a conditional formatting rule. To set that up, I’ll select the entire column and under that Format menu, click on the option for Conditional formatting. In that section, there will be an option to Add another rule. And under the drop down for Format cells if…, I select the option that says Custom formula is. And in that box, I’ll enter in the above formula:

Creating a conditional formatting rule in Google Sheets.

I’ll leave the default highlighting options, and now it will highlight all the values that show up more than once in column B:

Table with conditional formatting rules applied.

As you can see, there are many repeat winners in this list. If I only wanted to see the winners that only won once, then I would adjust the formula so that it looks for a value of equal to one, as opposed to more than one.

=COUNTIF(B:B,B1)=1

By altering the formula, it will highlight only the values that show up once:

Conditional formatting showing only values that show up once.

You could also go further and make even more specific conditional rules, such as highlighting countries that have won two or more times. Through conditional formatting, you can make your highlight rules as specific as you need them to be.

Extracting and counting unique values

If instead of getting the duplicates you wanted to just get a list of unique values, that’s an even easier process in Google Sheets. Using the UNIQUE function, all you need to do is select your range, and Google Sheets will give you a list of the unique values:

=UNIQUE(B2:B22)

This formula results in the following list:

Using the Unique function in Google Sheets to extract a list of unique values.

There have only been eight countries that have won the World Cup heading into 2022. But suppose you only wanted to count the number of unique winners. For this, you can use the COUNTUNIQUE function, which takes the same range as the argument:

=COUNTUNIQUE(B2:B22)

The above formula returns a value of 8, which is the same if I were to count the number of values from the Unique formula. There’s also the COUNTUNIQUEIFS function that you can deploy which allows you to also apply an IF statement to the CountUnique function. Suppose I wanted to count the number of unique winners after 1980, that formula would be as follows:

=COUNTUNIQUEIFS(B2:B22,A2:A22,">1980")

Column A contains the year and this returns a value of 6, excluding the two countries that only won prior to 1980: England and Uruguay. Using this function, you can apply multiple criteria if you need to.

If you liked this post on How to Find Duplicates and Unique Values in Google Sheets, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Do a Lookup with Multiple Criteria in Excel

November 2, 2022

by howtoexcel countif index lookup lookups match sumifs vlookup

Most Excel users likely know how to do a simple VLOOKUP and pull in data where a single field is matched. But what about when you need to match multiple fields? That can be a bit more challenging to pull off and below I’ll show you a couple of ways you can achieve this.

In the following data set, there is information on airports and delay times by specific carriers. Given that there are so many fields here, a simple lookup wouldn’t be helpful here as you need to factor in multiple criteria.

Data showing carrier delays at airports across the world.

Using a consolidated unique key

If you are able to create an additional column in your data set, then one option you have available is to create a unique identifier. For example, if I concatenate the carrier code, airport code, month, and year, I can have a key that I could use in a lookup formula. Here’s how that key could look:

The important thing to remember here is that your key should be unique enough so that there is only a single match. For example, if I didn’t include the year and my data includes multiple years, I could potentially have multiple matches for a combination of month, carrier, and airport code.

One way you can test for this is by using the COUNTIF function. This will tell you if there is more than 1 instance of a value. If my key is in column B, here is how that COUNTIF function could look:

=COUNTIF(B2,B:B)

If any of the formulas return a value of more than 1, then that will tell you there is a duplicate value:

Using the COUNTIF function to determine if a key is unique.

You can use filters to see if there are any values greater than 1 on this list. If there are, then you know you need to adjust your key to add additional criteria so that there are no duplicates. Once you have this accomplished, then you can use this within a VLOOKUP or a combination of INDEX & MATCH. You would just need to use a search criteria that follows the same construct.

Using multiple criteria in a SUMIFS function

Another way you can lookup multiple fields is by using SUMIFS. You can sum the data but you don’t have to. After all, if your criteria is unique, a SUMIFS function would only be summing a single value. And in that sense, it can work similar to a lookup. Using this approach, you don’t have to create any additional columns to make it work.

Here’s how a formula in my data set would look like if I wanted to extract the carrier_delay value for Delta Air Lines (carrier code DL) at the Atlanta airport (airport code ATL), for July 2022:

=SUMIFS(Q:Q,A:A,2022,B:B,7,C:C,"DL",E:E,"ATL")

Q is the value I’m extracting, column A relates to the year, column B to the month, column C to the carrier code, and column E to the airport code. Since there is only one corresponding value when filtering for all these combinations, I know my SUMIFS function is only pulling in a single value, and thus, working effectively as a lookup function.

With this approach there is some risk if you don’t first vet your criteria and to check for duplicates. And just to be safe, you’ll probably want to do a check for that before relying on this calculation.

If you like this post on How to Do a Lookup with Multiple Criteria in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Use COUNTIF in Excel

May 4, 2021

by howtoexcel with No Comment beginner count countif functions

Do you want to learn how to quickly count the number of cells that meet certain criteria? How about partial matches using wildcards? Below, I’ll show you how you can do this using the COUNTIF function in Excel along with similar tasks.

How does the COUNTIF function work?

As the name suggests, the COUNTIF function in Excel will count the values in a range if they meet certain criteria. It is not case-sensitive and in most cases, people use it for entire matches. However, you can also use it if you want partial matches.

In the data sample below, I have a list of the largest stocks on the North American markets along with the sectors that they are in:

List of the largest public companies in the world.

In total, my list contains 1,000 companies. To count the number that are in the Computers and Technology sector, I can do the following formula:

=COUNTIF(B:B,”computers and technology”)

Column B is where the sector name is. The above formula returns a value of 170. You’ll notice in the formula I didn’t bother matching the case because it isn’t case-sensitive and doesn’t matter how you enter the criteria in.

A better way to set this up is to reference an actual cell rather than hard-coding the criteria. This can help prevent errors and you don’t have to go into the cell to see what it is searching for. Here’s what the formulas look like:

I also added a SUM function at the bottom to see how many of the sectors are accounted for. With these formulas in place, I can easily copy down these functions to accommodate more sectors if I need more. This is what the COUNTIF function looks like in its simplest form. Next, let’s use wildcards to take it to the next level.

Using wildcards in a COUNTIF formula

There are two sectors in this data set that are similar — consumer discretionary and consumer staples. If I use the approach above, I would need to create COUNTIF formulas for both of them and then total them up:

This isn’t optimal and since the word ‘consumer’ is in both sectors, I can just have the COUNTIF function look for that, rather than creating two separate formulas and then a third to total them. To accomplish this, I’m going to use a wildcard to just look for the word ‘consumer’ :

=COUNTIF(B:B,”consumer*”)

You’ll notice the asterisk at the end of the word ‘consumer’ which will ensure that it will also include any text that comes after it. But how can this work to make the formula dynamic and reference a cell? To do that, I’ll use the & to connect the string to the asterisk:

D2 is where the consumer value is, and by linking that with the asterisk (*) it still allows the cell to be dynamic. In the following example, I put the asterisk at the end of the text but you can also put it at the beginning if you want the value to end with the word:

In my data, there is nothing that starts with trucks, but there are 30 values that end with it. The second formula counts those that end with the value. But what if you don’t care and just want to count every instance, regardless of where it is in the text? In that case, just add the asterisk before and after the criteria:

Using wildcards before and after a string of text in a COUNTIF formula.

Suppose I just wanted to count all the sectors that included the letter ‘s’ :

A total of 709 sectors include the letter ‘s’ in their descriptions.

Using COUNTIF with blanks

You may also want to calculate how many of the cells are blank, nonblank, or don’t contain anything. Let’s cover those sections below:

Counting blanks cells

To count all the blank values you have two options. You can use the COUNTIF along with an empty string (“”) or you can use the COUNTBLANK function if it is available on your version of Excel. Both can generate the same results:

Since I’m looking at the entire column, there are many blank cells in my entire range.

Counting nonblank cells

If you want to count the cells that have values in them, this is what the COUNTA function is used for:

My data set had 1,000 values in it and with the header, and so the formula returns a correct result of 1,001.

Using COUNTIF to count numbers

So far, I’ve covered how you can use the COUNTIF function in Excel with text. But you may also want to count numerical values as well. In this example, I am going to pull in the market capitalization of each of the stocks listed earlier. Here’s what that looks like:

You can use the COUNTIF function like with text but exact matches aren’t as useful when it comes to numbers. Neither are wildcards. Using the greater than (>) or less than (<) operations will be much more helpful in this situation.

Let’s start with a scenario where I want to count all the stocks that are worth more than $1 trillion. To do this, my formula looks as follows:

=COUNTIF(B:B,”>1000000000000″)

Like with the wildcard, the greater than sign goes within the quotes, as does the number. You can also connect this to a cell using the & sign to make it more dynamic:

By referencing a cell and applying a number format, it is also easier to read the value than having to rely on counting the right number of zeroes within the formula. This formula correctly returns the number 5, indicating the number of stocks on the list with valuations of more than $1 trillion. I can copy this formula down and apply it to other valuations as well:

Counting the number of companies based on their valuations.

Each threshold tells me the number of stocks that are worth at least that value. But what if I don’t want to overlap and just want to know the number of companies between $500 million and $1 trillion? To do this, you will want to use the COUNTIFS function, which allows you to enter multiple criteria. It works similar to the COUNTIF function and you just continuing adding a pair of arguments (one for the range, the other for the criteria) until you are done. To count the number of companies that fall within $500 million and $1 trillion, my formula would look as follows:

=COUNTIFS(B:B,”>500000000000″,B:B,”<=1000000000000″)

In this example, I also included the equals ‘=’ operator so that it includes values that are less than or equal to $1 trillion.

This is how it might look in a table where you want the values to update dynamically:

Counting the number of companies that fall within a range of valuations.

In the first row, the COUNTIFS function isn’t needed since that is only looking at one criterion. But for the other calculations, it is pulling in only the values that fall within that range ensuring that they don’t overlap with other categories.

If you liked this post on how to use COUNTIF in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Table containing numbers specifying if they are even or not.

How to Count the Number of Cells With Text in Excel

April 7, 2017

by admin with No Comment arrays count countif intermediate len

Counting blank and non-blank cells is fairly straightforward, but what about the cells that have formulas in them that don’t return a result and look blank? They can distort those calculations. In this post, I’ll cover how to count the number of cells with text in an Excel spreadsheet (regardless of if they contain formulas or not), using multiple approaches.

I’ll use the table below for the basis of my calculations which includes some values that look empty (even though they aren’t).

In column A I have the numbers from 1 to 11 listed. In column B I have a formula to determine if the number in column A is even, and if it is, I will place the word EVEN as my result, otherwise, it will be blank. The formula I used is the MOD function, which tells me how many remainders there are after dividing by a number. It has two arguments: the number I want to divide, and by what factor. My formula in cell B2 looks as follows:

=IF(MOD(A2,2)=0,”EVEN”,””)

In the above example, I am dividing cell A2 (1) by 2 and saying if it equals 0 (suggesting no remainder), then I want the result to return the word EVEN, otherwise, I want the cell to be blank (“”). Since 1 divided by 2 does have a remainder, the result in column B is a blank value (“”). In the next row, since the number 2 does not have a remainder, the result in column B is “EVEN.”

All the cells from B2:B12 have formulas, although some look empty.

Using COUNT, COUNTA, COUNTIF Functions

The conventional way to count non-empty cells is using the COUNTA function. A:

>=COUNTA(B2:B12)

The above formula will return a result of 11, since all 11 cells in the range are not empty, which is correct. But this doesn’t tell me how many actually contain values. If I wanted to count how many cells had numbers, I would use the COUNT function. This won’t count text, however.

=COUNT(B2:B12)

The above formula yields a result of 0, since there are no numbers in that range, otherwise, it would have worked fine. One workaround I could do is the COUNTIF function. I can count the number of blanks(“”) in the range:

=COUNTIF(B2:B12,””)

This returns a result of 6. I could combine the COUNTIF and COUNTA functions to arrive at my answer as to the number of cells that contain values that aren’t formulas:

=COUNTA(B2:B12)-COUNTIF(B2:B12,””)

This will result in 11-6 = 5. In Excel, there is usually not one way to solve a problem, so I’ll show you another way to accomplish this.

Using An Array Formula

The great thing about array formulas is they allow you to do multiple things in one formula that you couldn’t otherwise do with regular formulas (at least, not in one step). I am going to use the LEN function which tells me the length of a cell. If a cell is empty, it will return 0. If there is even one letter or digit, LEN will equal 1. I want to evaluate every cell’s length, and then tally all those that have a length of at least 1.

The LEN function would look as follows:

=LEN(B2)

This will result in a value of 0, since cell B2 has nothing in there (even though a formula exists). It is a simple function with only one argument as you can see. I will go a bit further and combine it with an IF function to return a value of 1 if there is something in the cell, and a value of 0 if there is not.

=IF(LEN(B2)>0,1,0)

The last step is to use the SUM function to now total all these values. If the non-empty cells return values of 1, then I just need to sum them all of them to get my count. The formula (before turning into an array) looks like this:

=SUM(IF(LEN(B2:B11)>0,1,0))

All I have added is the SUM function before my IF function, as well as an additional closing bracket. To turn it into an array formula, when editing the cell I have to click CTRL+SHIFT+ENTER and my formula will now look as follows (on newer versions of Excel you don’t need to do this):

{=SUM(IF(LEN(B2:B12)>0,1,0))}

This will return a result of 5 which correctly returns the same result as when I combined the COUNTA and COUNTIF functions. Below is a summary of the results:

Excel spreadsheet showing a variety of count formulas.

If you liked this post on How to Count the Number of Cells With Text in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.