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