COUNT vs SUM vs COUNTA vs COUNTBLANK vs COUNTIF

COUNT vs SUM vs COUNTA vs COUNTBLANK vs COUNTIF

Many people get confused about how and when to use some of the basic Excel functions. For example, when do you use SUM and when do you use COUNT?

In a moment I’ll tackle that specific question.

Then I'll bring 3 other COUNT functions into the mix - COUNTA, COUNTBLANK and COUNTIF and show you where you would use each one.

Feature Download: Grab a printable PDF of everything in this post (completely free). Never forget a key point again!

What is the difference between SUM and COUNT?

Very simply, SUM calculates a total for a number of cells or values, so it’s answering the question: HOW MUCH? Or, WHAT IS THE TOTAL?

COUNT tells you HOW MANY cells meet a certain condition.

Consider the following data:

SUM vs COUNT

Figure 01: SUM vs COUNT

  • Cell A6 uses a SUM function to add up the values in cells A1 to A6.
  • Cell C6 uses a COUNT function to find how many cells in the range C1 to C6 contain numbers. The COUNT function ignores blank cells or cells that contain text or symbols.

Other COUNT functions in Excel

There are number of other functions available in Excel. Heres a quick summary of what they do, followed by an example of each.

  • COUNT counts how many cells in a range that contain numeric data (numbers).
  • COUNTA counts how many populated cells in a range (i.e. not blank).
  • COUNTBLANK counts how many blank cells in a range.
  • COUNTIF counts how many in a range that meet a certain condition.

Consider the following data:

Sales made by the sales team

Figure 02: Sales made by the sales team

Here’s the results for each formula:

=COUNT(B1:B11)

Answer = 5.

=COUNTA(B1:B11)

Answer = 7.

=COUNTBLANK(B1:B11)

Answer = 3.

There is no single function that tells you the number of text cells but you can work it out with this formula:

=COUNTA(B1:B11) - COUNT(B1:B11)

To demonstrate the COUNTIF function, consider the following data:

A table showing stats for some trades people

Figure 03: A table showing stats for some trades people

The COUNTIF function needs 2 bits of information - the range of cells you are looking at and what it is that you’re checking for. The criteria is always encapsulated in double quotation marks (“) and is not case sensitive.

Example of the COUNTIF function

To find how many tradespeople drive a Toyota:

=COUNTIF(C2:C23,"Toyota")

To find how many plumbers there are:

=COUNTIF(D2:D23,"Plumber")

To find how many tradespeople charge more than $70 per hour:

=COUNTIF(E2:E23,">70")

To find how many of the tradesmen’s names start in the last half of the alphabet:

=COUNTIF(B2:B23,">M")

I hope you found this post useful. Please share and add a comment if it helped you. Thanks muchly.

Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature

Jason Morrell photo and bio

Cheat Sheet - The COUNT Family

FREE CHEAT SHEET

Download a printable PDF of this post

PLUS, GET A FREE COPY OF MY 30 EXCEL POWER TIPS

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz