The three IFs

These are:

• COUNTIF
• SUMIF
• AVERAGEIF

Actually the title of this post is not absolutely correct, as the last mentioned function is only available in MSXL 2007 and newer. So it is more two and a half IFs really...  Imagine we have a table with some sales information of our local tool shop

Display Excel tables online >> Excel Jeanie HTML 4 

COUNTIF - Counting appearances

The first thing we will focus on is the number of products sold for each Sales Manager as well as the number of each product itself.

This is a good example for the countif function. As each name is mentioned once per each individual sale, we will use the following formulas:

For Bob we use: =CountIf($A$2:$A$25,"Bob")

The two elements of this formula is the range in which we search - here cells A2 to A25 - and the criteria to search for which is "Bob" for the first result.

To get the counts for the other three guys we will have to replce the criteria - "Bob" in this case - with the corresponding names "Peter", "James" and "Dick".

The next thing we want to do is count the sales by product so the formula goes like this:

For the sold saws I have used: =CountIf($B$2:$B$25,"Saw")

This time we had to limit our search on column B because all products are listed there. If you want to reuse the first formula you have to enlarge the area to search in to A1:B25 because this covers both, Sales Managers and products.

Again we have to replace the second element - the criteria to look for - witht the ones we want to count (Hammer, Wrench, Screwdriver).

Please note that we will used the "$" sign for all formulas because if we simply copy the formulas for the next sales manager or product, it could be that we do not get all the values we need although this does not apply to this example because of the structure of the table. 

SUMIF - Adding up values

The next thing we want to know is how much sales have been made by each sales manager. We will use the SumIf function for that.

The formula to be entered goes as follows: =SumIf($A$2:$B$25,"Bob",$C$2:$C$25)

This function consists of three elements:

1. The range to search in which is - as we want to reuse the formula for the next step - A2 to B25
2. The value to look for. In this example we want to know Bobs sales so we use "Bob" here
3. The range to sum. As it makes no sense to sum the appearances of Bob in this table (we used CountIf for that) we needed to define, what range should be summed. In this example the values are in column C so we used the range C2 to C25 for that. This last element is optional for the formula to be working but for our purposes we needed something to sum up so we used this third element.

AVERAGEIF - Determining average values

The last function we will use is called AverageIf and is available in MSXL 2007 and newer.

We now want to know the average sales of every sales manager.

For Bobs sales we use: =AverageIf($A$2:$B:$25,"Bob",$C$2:$C$25)

The elements of this funtion are nearly the same as in the SumIf function. We have an area to look in, a criteria to look for and an area to average. The only difference between the two is that the latter function averages the values where the SumIf function adds them up.

I have uploaded two files (one with and one without the AverageIf fucntion) so you can have a look and play around with it.

Download the example files (ZIP, 10kB)

This finalizes todays post on these very handy and often used MSXL functions. I hope that it helps you in your daily work. If you have any notes or examples where these formulas come in handy please leave a comment or share your experience with it.

Discuss this blog post in the forums (click the blue button)