COUNTIF and SUMIF Formulas in Microsoft Excel

Excel offers two simple built-in formulas to tackle counting and summing in rows or columns based on criteria that you establish. The formulas discussed in this article are COUNTIF and SUMIF.

COUNTIF

This formula will analyze data in a range consisting of single row or column, or adjoining rows or columns. For example, you could analyze data in the column defined by A1:A20, or you could be looking at A1:B20. Either range is acceptable to use for the COUNTIF function. The form of the formula is:

=COUNTIF(Range,Criteria).

Let us say, for example, that you are interested in determining the total number of sales in a region, with regions being defined as North, South, East and West, and you have daily sales for each region for a five-day period.



The first step might be to determine how many sales over the five-day period exceeded 30 units per day. Further assume that our region names are in the range C6:C9 with corresponding unit data in D6:H9. The formula to find unit sales over 30 would be =COUNTIF(D6:D9,">30"). This will give you the correct answer. Note that in this function, the criteria portion needs to be in quotes. You could also have that linked to a cell, such that the formula could be =COUNTIF(D6:D9,A6), where the cell A6 would have =">30" in it. Sometime, linking to another cell provides more flexibility to a financial model, especially is you are doing a quick sensitivity analysis.

The COUNTIF function has some flexibility to count based on wildcards, like creating criteria that is "*py" to find all words that end in "py" or containing "py" would be "*py*" and so on. There are advanced criteria that will be covered in my blog, but for now, we want to keep it simple.

The major drawback to the COUNTIF function is that is can only take a single criterion to analyze. So, if you wanted to determine all of the unit sales over 30 AND within the North region, you would have to do a formula like the following:

=SUM(IF(C6:C9="North",(IF(D6:H9>30,1,0),0)).

In this case, you would have to use Ctrl+Shift+Enter to get the right answer (this is array formula entry, which is more advanced than this article intended, but is necessary for your understanding). This formula will tell you how many days in the five-day analysis in which unit sales exceeded 30 in the North region.

SUMIF

The SUMIF formula is similar in its constraints to the COUNTIF formula, only being able to assess one particular criterion. The form of this function is:
=SUMIF(Range,Criteria,Sum_Range).

Let us assume that you need to know the total of all unit sales in the five-day period for days in which the unit sales exceeded 30. The formula would be:

=SUMIF(D6:H6,">30",D6:H6).

This literally breaks down into looking at the range defined by D6:H6, finding all values greater than 30, and then summing those values within the range of D6:H6. This seemingly simple example is only meant to convey the context by which you would use this formula. A more likely scenario could involve a long list of salesforce member names, defined by the range of A1:A200, with the results of last year's sales by product in B1:B200. If you wanted to know the total sales of all products by name, the formula would be:

=SUMIF(A1:A200,"Name of Key Employee",B1:B200).

This is the more likely use of the SUMIF formula. Once again, it bears mentioning that the shortcomings of this formula minimize its overall usefulness. In simple database settings, it will work fine, but if you have a large database of information such as sales, profits, units sold and inventory, all by salespersons, you are likely to want to better understand the array formula entry as it will be more flexible and easier to manipulate to provide a variety of the desired results.

LOOKUP Function in Microsoft Excel

In contrast to the VLOOKUP and HLOOKUP functions in Excel, the LOOKUP function has two forms: vector and array. The following is a brief description of the differences in the two approaches.

Vector Form

Under the vector method, the formula you create will look for the cell you target, within a range of information (a single row or column) with the result you seek in some other range of information (another single row or column of same size as the first). Let us say hypothetically that you have a list of fruits whose sales prices vary by month, and you are curious as to the price of bananas in the month or March. We will assume that your list of fruit names is in column A beginning in row 3 with data through August (column I).


Thus, the entire range of data is within A3:I9. We would like to get the data for March, which, in this case is in column D. This means that the LOOKUP formula would be:

=LOOKUP("Banana",A3:A9,D3:D9), to yield the price per pound of bananas in March.

This would tell Excel to find the word "Banana" in the range of fruit, and whatever placement number within the column results, that same placement number will be used in the range for March to return the desired result. Similarly, if you wanted to find lychee values for August, the formula would be:

=LOOKUP("Lychee",A3:A9,I3:I9), to yield the price per pound of lychee in August.

To increase the efficiency of using this method, particularly if you have a lot of columns representing data for multiple years, is naming the columns. For example, if you name A3:A9 "Fruit_Range" and I3:I9 "August_Data" then the formula for the lychee example we just reviewed becomes:

=LOOKUP("Lychee",Fruit_Range,August_Data)

or

=LOOKUP(A8,Fruit_Range,August_Data), where the cell A8 contains the word lychee.

Why is this important? If you have a situation that requires looking up 1,000 fruit names across 36 months of data, the copying and pasting becomes more efficient when you need to create summary reports. This will be covered down the line with other articles, but just keep in mind that when you develop spreadsheets, efficiency and flexibility should be main drivers behind how you construct a financial model.

Array Form

The array form is very straightforward, in that you can either setup a long formula with specific references or highlight a set of data that comprises the range of information you are using. A simple example would be:

=LOOKUP("k",{"a","d","k","z";3,6,9,12}), which would find "k" in the array defined by "a","d","k" and "z" and return the appropriate value from the array defined as 3,6,9,12, and in this case, the answer would be 9. You could also have =LOOKUP("kangaroo",{"a","d","k","z";3,6,9,12}) and the result would still be nine. The point here is that if it is not a specific match, similar to VLOOKUP and HLOOKUP, Excel looks for the largest datapoint smaller than or equal to the reference value ("kangaroo", in this case). Finally, you could write this equation in the equivalent form of =LOOKUP("kangaroo",{"a",3;"d",6;"k",9;"z",12}). Note that the arrays could be flipped, to have Excel search a number lookup and return a letter or lookup a number to return another number or any assortment of combinations. As long as the arrays have the same number of values, you will not get an error message.

The other method I referenced relates to grabbing a range of data. For example, if you have colors listed in column A with unit sales data for four days in the subsequent columns, the entire range of data is comprised in the area defined by A2:E6.


If you wanted to know how many units were sold on day 4 of oranges (with "orange" in cell A6), you would enter the following formula:

=LOOKUP("orange",A2:E6) or =LOOKUP(A6,A2:E6), and the correct result would be displayed.

I have not used this form of the LOOKUP function, and it seems easily replaced by VLOOKUP. In addition, another drawback of this method is that if your column inputs (first column) are greater than the number of total columns you have in the data range, you will get a false answer (you can test this by creating a simple table as outlined above and adding a column "F" with data, changing the range to A2:F6 and you will get the answer "orange" instead of a number").

Another drawback to this method is a drawback of the vector form, too. There is no option to tell this formula to find the exact data point as in the case of VLOOKUP or HLOOKUP. There are ways around that, but for now, this standalone function will always find the closest value only.

Finally, either form requires the first column data to be in ascending order or the results may be correct. Based on the prior paragraph, you must make certain that the data is in ascending order or you will need to rely on another method to do the lookup you need.

While I do not use either of these functions extensively, the vector form is my preference if I have to use one or the other. Once you make the range reference absolute (the dollar signs in front of the letter and the number, like $A$2:$E$6), you can insert rows or cells and the values will maintain their correctness. The feature is something that I always consider when developing financial models.

VLOOKUP and HLOOKUP Functions in Microsoft Excel

One of the more efficient and simple methods to reference an existing database or collection of data is through the VLOOKUP, HLOOKUP or LOOKUP functions. The VLOOKUP and HLOOKUP functions are similar and, therefore, have the same strengths and weaknesses. The LOOKUP function has two forms (vector and array), and should be used under different circumstances. I will cover the LOOKUP functions in the next post.

VLOOKUP and HLOOKUP

The VLOOKUP function can be used when referencing a particular name in the first column of a set of data, to pull information a specific number of columns away. For example, you could have the names of all of your salespersons in the first column and you would like to see how many sales that person made on the ninth day of the past month. The formula would look something like the following:

=VLOOKUP("John",A1:M20,9), where the range A1:M20 contains all of the data.

In this case, VLOOKUP will look for "John" in the first column of the cells covered by the range A1:M20, which is your range for all of the names and data, with the information you want in the ninth column INCLUDING the first column (that includes the various names of the salesforce). The VLOOKUP function searches A1:A20 for the name and will then look over the columns you have dictated to retrieve your data. If there is no exact match, Excel will return the value that is immediately less than what you are seeking. So if you had no "John" but there was a "Joe" and a "Josh," Excel would retrieve the value of "Joe" for you input.

Similar in construct to the VLOOKUP function, the HLOOKUP function is a way of finding information based on rows instead of columns. This would be useful in a case that has a series of dates across the top (like monthly or quarterly data) and the desired information is related to total sales for a particular period. One could setup the following to find such data:

=HLOOKUP("Q1 2008", D1:Q20,3), where the range A1:M20 contains all of the data.

In this case, HLOOKUP will search for the time period "Q1 2008" in the first row of the range D1:Q20 and return the information 3 rows down, including the first row, which is where the relevant information you are seeking is located. In this case, HLOOKUP searches D1:Q1 for "Q1 2008" and returns the value the number of rows away that you specified in the formula. As in the case of VLOOKUP, if there is not an exact match, the results will be from the row immediately less than what you are trying to find.

Note that in these two functions assume that the ordering of the first column (VLOOKUP) or row (HLOOKUP) is in alphabetical order. If that is not the case, neither of these functions will work properly. To get around this, we can setup the function to find the exact match we need by adding an additional command in the formula bar. Using our salesperson example again, let us assume that the names are in some other order than alphabetical, like descending based on last year's total sales or by birth date. We would do the following to adjust for such order:

=VLOOKUP("John",A1:M20,9,false).

The inclusion of the "false" at the end of the formula means that Excel must find the exact match you have targeted. If there is not exact match, the formula will return #N/A. You would use this same format for HLOOKUP when you want to find the exact match.

One major drawback of the VLOOKUP function is that if you insert or delete a column, the function will not compensate. In other words, if you delete a column and the formula you created is referencing column number nine, it will still reference number 9, which would now be the next column over. You would have to go back to the VLOOKUP formula and change the column reference number. The same effect occurs if you change the number of rows when using the HLOOKUP function. You need to keep cognizant of that as you start to modify the data range you are targeting, and make sure that the formula is correctly providing the information you require.

In summary, VLOOKUP and HLOOKUP functions are good for spreadsheets that are likely not to shift in construction (inserting or deleting columns or rows). It is a good basic formula for less dynamic spreadsheets, and is easily implemented by newcomers to financial modeling.

IF-THEN Statements with Microsoft Excel

One of the basic statements for financial modeling is the IF-THEN function in Excel. While this may not be considered as robust as other approaches when doing actual computer programming (C++, Java, VB), in Excel it is rather straightforward and ubiquitous.

There are a couple of things to remember when using the IF-THEN approach:

• For basic results like "if this cell equals dog put a bone in the cell next to it" or "if the cell on my right equals rain display an umbrella sign in the cell below" or something like that. IF-THEN works well for binary situations, and is a method that the basic financial modeler can use and understand readily easily. The beauty of the function at its most basic level is the simplicity.

• IF-THEN can also be used for non-binary situations, meaning that one can "nest" several sub IF-THEN statements within the cell. For example, let's assume that you had four triggering events: apple, orange, grape and banana. Depending upon the word that was in a cell, there would be one of four results. This would look like (note that the extra spaces are in there for formatting purposes of this blog - you would not have spaces after the commas):

=IF(A1="Apple", "Watch for worm", IF(A1="Orange", "Citrus", IF(A1="Grape", "Soon to be a raisin", IF(A1="Banana", "Don't slip on the peel", "Do nothing")))).

You will notice that the last bit of this text includes "do nothing", which captures any text that is not one of the specified amounts you are seeking. If there were only four options (that somehow the input cell was blocked to prevent anything other than those four choices), the code would look like this:

=IF(A1="Apple", "Watch for worm", IF(A1="Orange", "Citrus", IF(A1="Grape", "Soon to be a raisin", "Don't slip on the peel"))).

By definition, only four choices will yield three IFs – think about the fourth IF as silent to help you see why this is the case.

• The financial modeler can also link IF-THEN statements to apply a mathematical operator to two or more statements in the cell. Thus, you could create a formula like =IF(A1="Pie", 1, 0) + IF(B1="Soup", 2, 0) + IF(C1="Water", 3, 0). You can do this with any operator and is useful if there are discrete components to the model you are developing. As you ultimately get further into advanced modeling, this can be used for triggers, or cells that create an influx of additional information when turned to a true state. Further discussion on that will occur as we move to intermediate level issues, but just remember that there is flexibility in the IF-THEN statement from this particular perspective.

• One shortcoming of the IF-THEN statement usage is that you can only have up to seven nested statements EVER in any function in Excel. For examples, the following is a maxed out version of the IF-THEN statement:

=IF(C5=1, 1, IF(C5=2, 2,IF(C5=3, 3, IF(C5=4, 4, IF(C5=5, 5, IF(C5=6, 6, IF(C5=7, 7, IF(C5=8, 8, "No")))))))) – if you try to add another nested IF statement, you will get an error message. Part of the rationale behind that is because evaluating the IF-THEN in the standard Excel environment (as well as computer language in general) takes some time to evaluate. Granted, it is not a lot of time when thinking about a machine calculating something faster than us, but each of the evaluation steps requires computing speed and memory. If you can imagine a nested 20-step IF-THEN statement in your mind and pretend that you must evaluate that statement in order until the last part of it, you will begin to see why there needs to be extra time for the computer to do analyze it. Thus, seven is the cutoff point, but in reality, you should never need that many nested IF-THEN statements anyway.

In summary, the IF-THEN statement can be a very useful, simple approach to binary and certain non-binary situations. It is not perfect for larger database inquiries or very large spreadsheets wherein the answer to the IF-THEN drives the model, but it can suffice for everyday simple financial modeling applications, and it easy to implement and interpret.

Microsoft Excel – Overview of the Financial Modeling Process

For the novice financial modeler, knowing where to start a model can be a difficult and somewhat daunting task. Forget thinking about formulas, structure, flow and results, but how does one even begin the process? The good part about this problem is that when you have worked on or developed hundreds of models, the starting point for model creation much more simplified in one's mind. This, of course, it similar to needing experience to get a job, but requiring a job for experience - thus, where to start?

In general, I have found that the map for creating a successful financial model follows these steps:

• Identify the problem to be solved or purpose of the model

Why do I need a model? This could be something like simple calculations to see what car payments may be like over a fiver-year period or the creation of family budget. It may be a more advanced model that calculates changes in interest rates based on a debt refinancing or the impact to a company's earnings from an acquisition. These are reasons why a financial model may be required.

• Identify the variables or assumptions needed to populate the model

What factors impact the analysis I am trying to perform? Do I need to consider macro economic factors like interest rates and foreign currency exchanges? Are there commodity pricing issues that will impact my analysis? If modeling a company's forecast, what sorts of growth expectations exist and what level of profitability in the foreseeable future? It is important at this point to make these mental assumptions prior to trying to create the model. The clearer one is in thinking through the steps, the easier the development will be.

• Think about the results (the answer you are seeking)

This may seem odd to think about what the answer to the question is prior to developing the model, but this helps form how the model is to be developed. For example, if you are curious to see what net income a company will generate based on a series of assumptions, you are likely to lean toward a standard income statement development with some for of summary table. If you are more interested in creating a Black-Scholes options pricing model, than the assumptions are relatively straightforward, and the answer you are seeking is the value of a call option or put option. If you are trying to determine which salesforce sells the most of widget A in the first week of each month for the past five years, the resulting model needs to have a summary that is easy to read expressing that exact answer. As you think about the purpose of the model and the assumptions affecting the output, you should begin to visualize the model in your head.

• Start building

Once you have the first three components determined, it is time to begin constructing the base of the model. For example, a typical leveraged buyout or acquisition model will have an income statement, balance sheet and cash flow as the primary driver of results. These are all linked together and require some basic concepts of accounting to be understood. From this, it is common to incorporate debt repayment schedules, various financial ratios covering cash flow and interest, and value to the buyers. For you novices, this will become more evident over time as you further hone your modeling skills, so just have patience.

• Double Check

In my career in investment banking, nothing causes more angst (other than losing a deal) than receiving an analysis from the deal team and quickly noticing that there are obvious errors. I learned from the old school methods of modeling, which dictated printing out the entire model and double checking the formulas with my trusty HP and a pencil. It is not necessary to check every single cell, but you need to provide the model with a sanity check. This will also help you build more robust models, as you will eventually start double checking the materials as you are developing the model, thus saving time.

These five steps will allow you to become a more efficient model builder, and allow you to progress from novice to intermediate in no time. Subsequent postings will include very simple examples of spreadsheets to help the reader better understand the process of financial modeling. For now, please just try to visualize the five steps above and I will be back in the next post with some basic beginning building blocks.

Introduction to Microsoft Excel

This is the beginning of my blog, Excel Tips and Tricks. I know that there are many resources out there for spreadsheet tips and financial modeling guides, but this blog will be a tutorial based on my fifteen years of financial model development. I will provide step by step information in the posts to come on practical spreadsheet development.

My steps will range from basic model input to some more advanced approaches that incorporate some of the built-in functions that are rarely used. In addition, the later posts will delve into basic VBA coding to simply certain functions that you may require on a regular basis in your own programming.

The other element to this blog is to illustrate features that can be performed by others for the benefit of small businesses that lack a true CFO or financial person. Having worked with small business in the past (business plan writing, strategy assessment, financial modeling), I know that focusing on the day-to-day operations is the critical element, and some of the necessary financial planning takes a back seat at time. Financial models can be a powerful tool for assessing the prospects and strategy of a business, and is a function that is best outsourced for small businesses.

In any event, I hope that you, the reader, will find this tutorial helpful and allow you to become a better financial modeler in the upcoming weeks and months. Developing spreadsheets may not be fund for everyone, but the final product should, at a minimum, accomplish your specific goals and provide some sense of reward to the creator.