What will these functions do for me?

The SUMIF and COUNTIF functions in Excel are simple conditional functions that can help you analyze subsets of your data.

You may already be familar with the related functions, SUM and COUNT, which can be used to add or count every value in a column, row, or spreadsheet. What SUMIF and COUNTIF add to this is the ability to add or count only the values that meet certain criteria.

Sample SUM

In this spreadsheet, we have names, ages, something summable, and gender of a number of people. <Add a field that can be summed…>

Sample SUMIF

In this spreadsheet, we have names, ages, something summable, and gender of a number of people. <Add a field that can be summed…>

To use a SUMIF, you need to tell Excel what range of cells you want to examine, and what criteria you want to use to determine whether to count a particular cell. The format for the SUMIF function is =SUMIF(range, criteria).

  • Range means where you are looking.
  • Criteria means what you are looking for

Sample COUNT

We can use the COUNT function to count how many entries you have in a particular spreadsheet, or part of a sppreadsheet.

To count how many people are listed in this spreadsheet, we could simply count their names, using the formula =COUNT($A:$A). This counts all non-blank cells in column A and will give you an accurate count of the number of names entered in the spreadsheet.

Sample COUNTIF

What if you want to count only the Males in the spreadsheet? That’s when you’d use COUNTIF. The format for the COUNTIF function is =COUNTIF(range, criteria).

  • Range means where you are looking.
  • Criteria means what you are looking for.

On our spreadsheet, Males are designated with the letter “M” in column C. So to look at column C and count all the males, we use the function =COUNTIF($C:$C, “M”).

CES / March 2007