Pinterest Stumbleupon Whatsapp
Ads by Google

Excel formulas can do a lot of really great things, from helping you get your finances together 10 Helpful Spreadsheet Templates To Help Manage Your Finances 10 Helpful Spreadsheet Templates To Help Manage Your Finances Wouldn't it be great if you knew where your money was, at all times? Read More  to helping manage your entire life How To Use Microsoft Excel To Manage Your Life How To Use Microsoft Excel To Manage Your Life It's no secret that I'm a total Excel fanboy. Much of that comes from the fact that I enjoy writing VBA code, and Excel combined with VBA scripts open up a whole world of possibilities.... Read More . 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 Mini Excel Tutorial: Use Boolean Logic to Process Complex Data 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:

Ads by Google

excel-column-count

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.

excel-countif-result

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:

excel-countifs-result

SUMIF

You probably know how to use SUM to add up a series of numbers (like Brad used in creating his Excel dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More ), 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:

excel-sumif-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.

excel-sumifs-result

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 Excel Vs. Access - Can a Spreadsheet Replace a Database? 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 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!

  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.

Leave a Reply

Your email address will not be published. Required fields are marked *