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])
``````

### sumif

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

``````=sumif([range],[criteria],[sum range])
``````

### 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])
``````

## 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])
``````=averageifs([average range], [criteria range], [criteria]",[criteria range2], [criteria2])