Using IF functions in Excel

When working with large amounts of data, the if family of functions in Excel are very powerful yet easy to use and allow you to extract significance in a very granular way.

While some of the functionality of the if functions can be found in the use of the simpler filters, the if functions give you much more flexibility when extracting significance from data.

Though it is a little more complex to type out an entire formula than it is to press the filter button, with a little experience creating complex if formulas becomes second nature.

if, countif, sumif, averageif

The if family of functions matches a single condition based on a logical test.

if

Performs a logical test on two values. If statements can be nested, however, it is now better to use ifs function when testing multiple conditions.


=if([logical test],[value if true],[value if false])
=if(c2>=b2,50,1)

countif

Counts the number of times an item occurs in a range based on a logical test. The range indicates the range of cells to examine for the criteria.


=countif([range], [criteria])
=countif(b2:b17,"=Google")

sumif

Sum the numbers in a range based on criteria in a second range.


=sumif([range],[criteria],[sum range])
=sumif(b2:b17,"=Google",c2:c17)

averageif

Average the numbers in a range based on criteria in a second range. averageif is almost exactly the same function as sumif except it averages the range rather than sums it.


=averageif([range],[criteria],[average range])
=averageif(b2:b17,"=Google",c2:c17)

ifs, countifs, sumifs, averageifs

The ifs family of functions can match multiple conditions. One important thing to note is that sumifs and averageifs reverse the sum and criteria range positions in the argument list compared to their singular counterparts.

ifs

The advantage of using the ifs function is that is replaces the practice of using nested if functions. This makes the resulting formula much more readable.

The format of the function is just a series of logical tests paired with the value to use if the test is true. If the logical test fails to produce a true condition then the next logical test is evaluated.


=ifs([logical test],[value if test results in a true condition])
=ifs(A1=B1,10,A1=B2,20,A1=B3,30)

countifs

The countifs function simply counts the number of times a value occurs based on multiple conditions being true. Just enter a pair of values for each criteria you want to test: where to look and what condition to check for. You can have up to 127 criteria tests.


=countifs([criteria range1],[criteria1],[criteria range2],[criteria2])
=countifs(A:A, "=Chrome", B:B,"=Windows")

sumifs

First range is the range of values to sum if the condition is true. Then a series of value pairs indicating where to look and what test to perform.


=sumifs([sum range], [criteria range], [criteria]",[criteria range2], [criteria2])
=sumifs(A1:A100,B1:b100,"=Google",C1:C1000,"=>1000")

averageifs

First range is the range of values to average if the condition is true. Then a series of value pairs indicating where to look and what test to perform.


=averageifs([average range], [criteria range], [criteria]",[criteria range2], [criteria2])
=sumifs(A1:A100,b1:b100,"=Google",c1:c100,"=>1000")