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
if family of functions matches a single condition based on a logical test.
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)
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")
Sum the numbers in a range based on criteria in a second range.
=sumif([range],[criteria],[sum range]) =sumif(b2:b17,"=Google",c2:c17)
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
ifs family of functions can match multiple conditions. One important thing to note is that
averageifs reverse the sum and criteria range positions in the argument list compared to their singular counterparts.
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 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")
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")
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")