Los Zombios: A digital marketing blog

A plain-talk digital marketing blog about analytics, marketing, paid search, content generation, SEO, and countless other cool digital marketing-related topics and interests.

Using IF functions in Excel 2016

Post Overview

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.

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. 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 if statements which can get rather difficult to read. Though the structure of the formulas is similar, one important difference to be aware of is that sumifs and averageifs reverse the sum and criteria range positions in the argument list compared to their singular counterparts.

ifs

As stated above, the huge 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 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)

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")

Wrapping Up

There are many very useful functions in Excel. Probably too many for any one person to ever expect to learn and use. However it is definitely worth while to explore the various functions in Excel when you have a moment and see which you can make use of on your day to day workflow.