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 from your data in a very granular way.
While some of the same functionality can be found in filters and slicers, the
if functions provide more flexibility. Though building a formula to extract data from a table is a slightly more involved undertaking than pressing a filter button or choosing a slicer, with a little experience, creating complex
if formulas becomes second nature.
This is one in a series of posts focusing on small groups of functions in Excel. Each post has a unifying theme or concept that unifies, to at least some degree, the relevant functions discussed in each post.
Making choices and setting conditions
The power of the
if functions lies in their ability to create logic branches by testing some condition and then responding differently depending on the result of the test. The most useful aspect of this ability is that data can be treated differently depending on the checked condition.
The common methodology shared by all the functions in the
if family is that they test of a condition is true or false and then take some action with the data based on the result of the true false test.
if, countif, sumif, averageif
This group of functions checks one condition and then interacts with the data given whether the condition is true or false.
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)
countifCounts 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")
sumifSum the numbers in a range based on criteria in a second range.
=sumif([range],[criteria],[sum range]) =sumif(b2:b17,"=Google",c2:c17)
averageifAverage the numbers in a range based on criteria in a second range.
averageifis almost exactly the same function as
sumifexcept it averages the range rather than sums it.
=averageif([range],[criteria],[average range]) =averageif(b2:b17,"=Google",c2:c17)
ifs, countifs, sumifs, averageifsThe
ifsfamily of functions can match multiple conditions. These functions are newer and may not be available if you are using an older version of excel. They are a nice way to avoid using multiple nested
ifstatements which can get rather difficult to read. Though the structure of the formulas is similar, one important difference to be aware of is that
averageifsreverse the sum and criteria range positions in the argument list compared to their singular counterparts.
ifsAs stated above, the huge advantage of using the
ifsfunction is that is replaces the practice of using nested
iffunctions. This makes the resulting formula much more readable and easier to write. 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. It is best to put the most likely to pass logical test first, then continue with the less and less likely ones until you run out of possibilities.
=ifs([logical test],[value if test results in a true condition]) =ifs(A1=B1,10,A1=B2,20,A1=B3,30)
countifsfunction 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")
sumifsFirst 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")
averageifsFirst 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")