Count and Sum Functions

Count | Countif | Countifs | Sum | Sumif | Sumifs

The most used functions in Excel are the functions that count and sum. You can count and sum based on onecriteria or multiple criteria.

Count

To count the number of cells that contain numbers, use the COUNT function.

Countif

To count cells based on one criteria (for example, higher than 9), use the following COUNTIF function.

Countifs

To count cells based on multiple criteria (for example, green and higher than 9), use the following COUNTIFS function.

Sum

To sum a range of cells, use the SUM function.

Sumif

To sum cells based on one criteria (for example, higher than 9), use the following SUMIF function (two arguments).

To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum).

Sumifs

To sum cells based on multiple criteria (for example, blue and green), use the following SUMIFS function (first argument is the range to sum).

General note: in a similar way, you can use the AVERAGEIF and AVERAGEIFS function to average cells based on one or multiple criteria.

Count Text Occurrences

This example teaches you how to count the number of occurrences of text in a range.

1. For example, to count the number of cells that contain exactly star.

2. For example, to count the number of cells that contain exactly star + 1 character. A question mark (?) matches exactly one character.

3. For example, to count the number of cells that contain exactly star + a series of zero or more characters. An asterisk (*) matches a series of zero or more characters.

4. For example, to count the number of cells that contain star in any way. No matter what is before or after star, this function finds all the cells that contain star in any way.

5. For example, to count the number of cells that contain text.

Count Logical Values

Learn how to count the number of cells that contain logical values (TRUE and FALSE) in Excel.

1. For example, to count the number of cells that contain TRUE.

2. For example, to count the number of cells that contain FALSE.

3. For example, to count the number of cells that contain TRUE or FALSE.

This example shows you how to count the number of blank and nonblank cells in Excel.

1. The COUNTBLANK function counts the number of blank cells.

2. The COUNTA function counts the number of nonblank cells. COUNTA stands for count all.

Sumproduct

To calculate the sum of the products of corresponding numbers in one or more ranges, use Excel's powerful SUMPRODUCT function.

1. For example, the SUMPRODUCT function below calculates the total amount spent.

Note: the SUMPRODUCT function performs this calculation: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.

2. The ranges must have the same dimensions or Excel will display the #VALUE! error.

3. The SUMPRODUCT function treats any entries that are not numeric as if they were zeros.

Logical Functions

If Function | And Function | Or Function

Learn how to use Excel's logical functions such as the IF, AND and OR function.

If Function

The IF function checks whether a condition is met, and returns one value if TRUE and another value if FALSE.

1. Select cell C2 and enter the following function.

The IF function returns Correct because the value in cell A1 is higher than 10.

And Function

The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false.

1. Select cell D2 and enter the following formula.

The AND function returns FALSE because the value in cell B2 is not higher than 5. As a result the IF function returns Incorrect.

Or Function

The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false.

1. Select cell E2 and enter the following formula.

The OR function returns TRUE because the value in cell A1 is higher than 10. As a result the IF function returns Correct.

General note: the AND and OR function can check up to 255 conditions.

Nested If

The IF function in Excel can be nested, when you have multiple conditions to meet. The FALSE value is being replaced by another If function to make a further test. For example, look at the formula below.

1a. If cell A1 equals 1, the function returns Bad.

1b. If cell A1 equals 2, the function returns Good.

1c. If cell A1 equals 3, the function returns Excellent.

1d. If cell A1 equals another value, the function returns No Valid Score.

Here's another example.

2a. If cell A1 is less or equal to 10, the function returns 350.

2b. If cell A1 is greater than 10 and less or equal to 20, the function returns 700.

2c. If cell A1 is greater than 20 and less or equal to 30, the function returns 1400.

2d. If cell A1 is greater than 30, the function returns 2000.

Note: to slightly change the boundaries, you might want to use "<" instead of "<=" in your own formula.