# Mini Excel Tutorial: Using Advanced Counting and Adding Functions in Excel 19-03-2015

## 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 Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Logical operators IF, NOT, AND, and OR, can help you get from Excel newbie to power user. We explain the basics of each function and demonstrate how you can use them for maximum results. Read More , 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

`=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.

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 Excel Vs. Access - Can a Spreadsheet Replace a Database? Which tool should you use to manage data? Access and Excel both feature data filtering, collation and querying. We'll show you which one is best suited for your needs. Read More , 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 7 Fun & Weird Things You Can Create With Microsoft Excel Imagine Excel was fun! Excel offers plenty of scope for projects that go beyond its intended use. The only limit is your imagination. Here are the most creative examples of how people are using Excel. Read More you can create in Excel.

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

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

1. Janet
April 28, 2016 at 9:45 pm

Help! I'm looking to return a value based on all of these:
1) date criteria (i.e. everything between 1/1/16 and 2/1/16) in the entire column A
2) text criteria (i.e. every entry with Hello) in the entire column C
3) the corresponding value when anything in column A and C match, and return that value in columns F and I, added together.
Have I explained that properly?
This is where I was going...
=SUMIF('Stats Data 2016'!\$A:\$A,">=1/1/2016", 'Stats Data 2016'!\$A:\$A,"<2/1/2016", 'Stats Data 2016'!\$C:\$C,"Hello", ['Stats Data 2016'!\$F:\$I])
I'm not sure how to accomplish this!
Thanks in advance for any help :)

2. Karl
March 20, 2015 at 1:15 pm

FWIW, Using Excel for Windows, I needed to put quotes around the conditional in this function:

=COUNTIFS(D:D, “Hawaii”, E:E, >1500)

to make it:

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

Thanks for the tutorial, and Good Luck!

• Dann Albright
March 23, 2015 at 8:00 am

Interesting! Thanks for pointing that out. That could be a Windows / Mac thing . . . you never know when something like that will pop up. What version of Excel were you using?

3. PJW
March 20, 2015 at 11:40 am

The images don't appear to show the correct results, is this because the dataset extends beyond the visible image?

• Dann Albright
March 23, 2015 at 7:59 am

Yes, the dataset is larger than you can see in the screenshots. I made the text larger and zoomed in to make sure that it was clear enough to read. Sorry for any confusion!

4. mma173
March 20, 2015 at 8:36 am

Throw in Sumproduct examples :D

• Dann Albright
March 23, 2015 at 7:58 am

I thought about doing SUMPRODUCT, but the article was starting to get a bit long and I wasn't sure how useful of a formula it was. But if there's interest in it, I'll make sure to get it in a future article!

5. YearOfTheGoat
March 19, 2015 at 3:43 pm

Thanks for such simple and clear mini tutorials. I know how to use Excel, but I like to find something If forgot or didn't know. Sometimes I search various tutors at other websites, but most of time I am scared away because of too much complexity and information overload. So I like mini-tutorial idea and expecting more :)

• Dann Albright
March 23, 2015 at 7:57 am

Yeah, Excel formulas can be really intimidating, especially when you start combining things and using optional arguments. But if you slow down and take a look at each thing in order, it's usually fairly straightforward. Glad to hear that you're finding it useful!

• Tina
March 29, 2015 at 2:56 pm

Thank you for reminding us why those articles are valuable, despite the sea of information out there. We do try to make information more accessible.

6. Pete Dooley
March 19, 2015 at 2:49 pm

Thanks again MUO...

7. Otto
March 19, 2015 at 2:44 pm

Love it. I use the basic functions but did not know about the multiple criteria. This will be useful.

• Dann Albright
March 23, 2015 at 7:57 am

Glad you found it useful! Using multiple criteria can be hugely useful.