IF Functions: Calculating Data If Criteria Is Met

by with No Comment functionsintermediate
An overview of how various IF functions work

IF Function
An if function creates a test, and can have different results depending on whether the test is true or false.

Example 1

 =IF(A1=”A”,1,0)
In this example, if A1 is equal to the letter A then the result of the formula is 1. If it is equal to B, C, or anything else, the result will be 0. It will look for an exact match – if you don’t want an exact match, you’ll need to use wildcards (see later down).
You can make nested If functions as well – instead of putting  a 0 in the formula above you can add another if function:
Example 2

=IF(A1=”A”,1,IF(A1=”B”,2,0))
In this formula, if the cell A1 does not match A, then it will do another test. The second test is checking to see if it matches B – if it does, it will assign a value of 2, and if not, a value of 0. You can keep adding If functions as much as you need to. But keep track of the parentheses to make sure they are properly closed. If you find yourself having to do many nested if functions then you may be better off using a lookup formula.
COUNTIF Function
The COUNTIF formula counts the number of items your criteria is met. See the example below:
Example 3
=COUNTIF(A1:A7,”A”)
This formula is looking at all the cells in the range A1:A7 and counting them if the match the letter A. This would result in 5. 
In newer versions of Excel, there is a plural version of COUNTIF– COUNTIFS. This allows you to use multiple criteria:

Example 4

=COUNTIFS(A1:A7,”A”,B1:B7,1)
This formula builds on the last, where it looks for the value A in cells A1:A7 and then also looks for the number 1 in cells B1:B7. This would result in 2. As there are two instances where A and 1 are in the same row. 
Note: If your ranges are not identical (e.g. A1:A7 and B1:B6), your formula will not work properly. Also, because in this example the formula was looking for a number, it does not have to be in quotations. You can keep adding more criteria by adding another comma instead of closing the formula.
SUMIF
SUMIF function works similarly to COUNTIF, the difference being is it can sum a separate range:
Example 5

=SUMIF(A1:A7,”A”,B1:B7)
In this formula, it will add all the values in B1:B7 when the corresponding values in A1:A7 are equal to A. This would result in a value of 10 (1 + 1 + 2 + 3 + 3). Like the COUNTIF function, SUMIF also has a plural version SUMIFS that can be used for more than one criteria.
There is a minor difference in how SUMIFS works:

Example 6

=SUMIFS(C1:C7,A1:A7,”A”,B1:B7,1)
In this formula, the values in C1:C7 will be added when the corresponding values in A1:A7 are equal to A and the values in B1:B7 are equal to 1. The key difference here is you select the range you want to sum first, and then the criteria comes after. In the SUMIF function, the criteria range came first, and then the range to sum came after. Similarly to the COUNTIF function, you can keep adding criteria with additional commas.
AVERAGEIF
This function works the same was as sumif, the main difference is instead of summing everything, it will take the average:
Example 7

=AVERAGEIF(A1:A7,”A”,B1:B7)
This formula will take the average of all the cells in the range B1:B7 where the related values in A1:A7 equal A. Similar to the way SUMIFS works, so too does AVERAGEIFS.
WILDCARDS
The above examples worked well if you wanted to match exactly what was in the cell. But what if you needed only to match one word or letter, or a portion? That is where wildcards become useful.
The first way to use a wildcard, is by using an asterisk (*). An asterisk represents unknown values before or after. For example:
Example 8
=COUNTIF(A19:A28,”P*”)
In this example, it will count the names that start with the letter P. Because the criteria start with P and is followed by an asterisk, what this means is that the first letter has to be a P, and the rest can be anything (three cells match this criteria)
Similarly:
Example 9

=COUNTIF(A19:A28, “*P”)
This formula works the opposite way, in that it will count the cells where P is the last letter, and any values can come before (no cells meet this criteria).
And also:
Example 10

=COUNTIF(A19:A28, “*P*”)
This formula will count the cells where P is anywhere in the cell (the same three cells that matched Example 8 are the only ones true here as well).
Note: the criteria here is not case sensitive.
But what if you are only looking for a certain number of characters? For example:
Example 11

=COUNTIF(A19:A28,”*H???”)
In this formula, it will count names that end with the letter h and 3 characters after. The ? represents one character (cannot be a number). This allows you to control your result to a finite length, whereas the asterisk doesn’t limit the number of characters. The cells that meet this criteria are A21 and A27.
A similar formula:
Example 12
=COUNTIF(A19:A28,”E??c*”)

This formula will look in the range and count the cells that start with the letter e, have any two letters after that, then a c, and anything else after that. The only result in the list is cell A24.

VLOOKUP vs INDEX and MATCH

by with No Comment indexintermediatematchvlookup
Lookups are popular in Excel and here I’ll look at the more popular one – VLOOKUP. However it’s not always the best option for doing lookups in Excel, and I’ll show you why.

VLOOKUP
Exact Matches
What Vlookup does is look for a value you have selected, and if it finds it, will return a value from the same table that corresponds to the matched value.
One of the key limitations of VLOOKUP is it cannot return results left of the matched item, only to the right. This is where I recommend the INDEX & MATCH formula (see later down), as that combination will allow you to go left or right and won’t require you to re-arrange your worksheet just to accommodate a formula.
Example 1

If I wanted to lookup value B in the table, I would enter the following formula:
=VLOOKUP(A7,A1:D4,2,false)
This will equal the value in cell B2, the number 2. If I changed the column number from 2 to 3:
=VLOOKUP(A7,A1:D4,3,false)
It would return the number 22, or cell C2.
If I selected column 5, it would result in an error because my table range (A1:D4) only contains four columns.
If instead of looking up letters in column A I wanted to lookup numbers in column B, I would have to change my table range from A1:D4 to B1:D4, and it would look like this:
=VLOOKUP(A7,B1:D4,3,false)
I would also have to change the value in A7 so that it is a number. But again, if I change the formula this way I cannot move to the left and find out what letter corresponds to my value. Not without re-arranging my table.
Note that before I changed the range column 3 related to column C, now it relates to D because the table has shifted. Column 3 relates to the column number in the table, not in the spreadsheet.
                                                                                   
Approximate Matches

The one strength of VLOOKUP is determining what category or range a value falls into. By changing the last argument in the formula to true, Vlookup no longer looks for an exact match. Why would this be useful? Let’s say you have the first 3 letters/numbers of a postal/zip code. Because there are so many combinations possible, you would have to list each one out to find an exact match. 

With VLOOKUP’s approximate match, it will determine the closest match (e.g. shipping rate codes won’t spell out an entire postal/zip code, but will often cite a range). Similarly, if you have tax brackets and need to know what bracket an income level falls into, this is where it would be useful as well. The one caveat is that the values in the table must be in ascending order.
Hlookup is a parallel formula to Vlookup, only that it looks horizontally rather than vertically.
Example 2
In this example, my formula looks like this:
=VLOOKUP(A7,A1:B4,2,TRUE)
The argument at the end has changed from FALSE to TRUE, meaning an exact match is no longer needed. If I had set it to TRUE, it would return an error. But in this case, it returns cell B2, or 2. The reason for this, is because the values are in ascending order, it correctly identifies that V3A falls between V2B and V3C. Since it has not yet reached V3C, it belongs to V2B. If V3A is changed to V4D, X, Y, or a value greater than V4D, it will equal 4, as it will recognize that it belongs to the highest category.

INDEX & MATCH
I mentioned using INDEX & MATCH will give you a more versatile formula. This formula is structured differently than VLOOKUP in that it will pull the coordinates from the row and column number you specify. The match function will allow you to determine the proper row number based on your search criteria, and the column number you can decide – whether it is left or right of the matched value, it doesn’t matter here.
Example 3
Going back to the Example 1, I’ll show you how using the INDEX & MATCH formula will be able to now move to the left and pull values from column A:
=INDEX(A1:D4,MATCH(A7,B1:B4,0),1)
The range is unchanged, but the second argument in the INDEX formula (relating to row number) is calculated using the MATCH formula. The MATCH formula looks for the value in A7 (1), in the range B1:B4 and returns the row number. The 0 in the MATCH formula represents an exact match. After the MATCH formula, the last argument in the INDEX formula is the column number, which has been set to 1, which will return the values in column A. The advantage of using INDEX and MATCH as you can see is you can change the column number to 1, 2, 3, or 4. Similarly, if you wanted to look up the values in column C instead of B, you would change the formula as follows:
=INDEX(A1:D4,MATCH(A7,C1:C4,0),1)
In this case you don’t need to change the column number, it doesn’t move since you don’t have to rearrange the table.
In short, when you should use either formula:
Looking up a value – INDEX & MATCH*
Finding a value based on ranges – VLOOKUP
*unless you just want a quick formula and the values you need are to the right