Excel formulas can do a lot of really great things, from helping you get your finances together to helping manage your entire life. You might think of counting and adding formulas as relatively mundane compared to more advanced formulas that are out there, but having a good handle on these functions can help you save a lot of time when you need to collect information about the data in your spreadsheet.

COUNT

The COUNT function counts the number of cells in a range that have numbers in them. If you use a formula to run a lot of calculations, you may find yourself wondering how many times it worked—COUNT can help here. If a formula returns an error (like #DIV/0!) for some entries, you can easily find out how many times that happened.

COUNT only counts a cell if it contains a number, a date, a logical value, or a text representation of a number. If you want to include cells that have text, error values, and empty text, use the COUNTA function. To do the opposite, use COUNTBLANK.

Here's the syntax for the COUNT function:

=COUNT([range1], [range2]...)

The [range] argument defines the range of cells that you want to do your counting in. In the spreadsheet below (data created randomly with www.generatedata.com), we have columns for first name, last name, city, state, and SAT score. Some of the SAT scores are missing, and we're going to find out how many. We know that there are 100 people in the spreadsheet, so we'll use COUNT on the SAT score column with the following syntax:

=COUNT(E:E)

Here's the result:

As you can see, 85 students have values in the SAT score column, so we're missing scores from 15 students.

COUNTIF

This function is very similar to COUNT, but lets you set the criteria for which cells get counted. Here's the syntax:

=COUNTIF([range], [value])

As with COUNT, the [range] argument is used to choose the range of cells in which you want to count. The [value] argument is where you put in your criterion. For example, if you want a count of all the students in the spreadsheet that live in Minnesota, you would use the following syntax:

=COUNTIF(C:C, "Minnesota")

You could use this method for counting the number of students who scored above a certain number, for example, by using ">1500" as your argument. You can also use a cell as a counting reference. We'll do that here to see how many students live in Texas. We'll use the following syntax:

=COUNTIF(D:D, D2)

The cell D2 contains "Texas," and we can use the cell reference instead of typing out the name of the state.

COUNTIFS

Whereas COUNTIF lets you count based on a single criterion, COUNTIFS lets you use as many criteria as you'd like. You can also use it to count based on multiple criteria in multiple ranges. Here's the syntax:

=COUNITFS([criteria_range1], [criteria1], [criteria_range2], [criteria2]...)

We'll use this to see how many students from Hawaii scored over 1500 on their SAT. Here's the syntax we'll use:

=COUNTIFS(D:D, "Hawaii", E:E, >1500)

When we run that, this is what we get:

SUMIF

You probably know how to use SUM to add up a series of numbers (like Brad used in creating his Excel dashboard), but what if you only want to add up a portion of those numbers? SUMIF lets you choose which cells are added by specifying a criterion. Here's the syntax:

=SUMIF([range], [criteria], [sum_range])

The [range] argument is self-explanatory, and the [criterion] argument can be a specific value, or a logical operator. For example, you could add all of the cells in a list that are greater than 500. The [sum_range] argument is a very useful one; if you input a range in this position, SUMIF will add the cells in a different range that are specified by the first range.

It's easier to use a visual explanation. In our example spreadsheet below, we have a list of names and a list of donations. To add all of the donations collected by David, we'll use this syntax:

=SUMIF(A:A, "David", B:B)

Here's the result:

Wherever Excel found "David" in column A, it added the value in column B.

SUMIFS

If you want to use multiple criteria to select which cells to add you can use SUMIFS. The syntax is a bit different than SUMIF:

=SUMIFS([sum_range], [criteria_range1], [criteria1], [criteria_range2], [criteria2]...)

As you can see, the [sum_range] argument comes first, instead of third, as it does in SUMIF.

I've added a column to our donations spreadsheet that contains the state in which the donations were gathered. Using SUMIFS, we can see how much John collected in Michigan with the following syntax:

=SUMIFS(B:B, A:A, "John", C:C, "Michigan")

Let's see what it turns up.

Counting and Adding Made Easy

While many Excel users are familiar with the SUM function, and quite a few have used COUNT before, getting to know COUNTIF, COUNTIFS, SUMIF, and SUMIFS can help you count and add a lot of information in a small period of time. There are even more SUM functions that you can use if you're working with arrays or databases, but those will have to wait for another time.

And if you're looking for something a little more out of the ordinary, check out these 7 fun and weird things you can create in Excel.

What do you use COUNT and SUM functions for? Share any tips you have below!