SUMIF, COUNTIF, and AVERAGEIF Functions – The Master Guide

This Excel Tutorial demonstrates how to use the Excel Countif and Countifs Functions.

Formula Examples: COUNTIF Function Description:

Counts all cells in a series that meet one (COUNTIF) or multiple (COUNTIFS) specified criteria.

COUNTIF Syntax

range – An array of numbers, text, or blank values.
criteria – A string containing the criteria. Example “>0”

More Examples:

First let’s look at an easy COUNTIF example:

COUNTIF Greater than Zero

This code will count all cells that are greater than zero in column A.
`=countif(a4:a10,">0")`

COUNTIF Less Than Zero

This code will count all cells that are less than zero in column A.
`=countif(a4:a10,"<0")`

COUNTIF Blank Cells

`=countif(a4:a10,"")`
This COUNTIF formula counts all the blank cells in column A. However, instead, you could use COUNTBLANK to count all the blank cells:
`=countblank(a4:a10)`

Count Not Blank Cells

Counting nonblank cells is a little trickier. You would think that this would count all the non-blank cells:
`=countif(a4:a10,"<>")`
and it usually does, except for one notable exception. When a cell contains a formula that results in “” (Blank), the above code will count it as non-blank because a formula exists in the cell. Instead, use this formula:
`=countif(a4:a10,"*?")`
This formula makes use of Wildcard Characters. We’ll learn about them below.

There is one other count function you should know: the COUNTA Function. The COUNTA Function counts all cells that contain anything: a formula (even if it results in “”), a logical value (TRUE or FALSE), text, or a number.

Count Blank and Non-Blank Cell Examples:

!!!!!!!!!picture of the various examples!!!!!!!!!!

(mention counta?)

Countif Wildcard

You may have heard about Wildcards in Excel. Wildcards are characters that can represent any character. Here’s a chart:
<>

picture with apples

Text – Exact Match

`=countif(a2:a10,"apples")`

Text – Contains Text

`=countif(a2:a10,"*apples*")`

Text – Contains Any Text

`=countif(a2:a10,"*")`

Countif – Does not Contain any Text

`=countif(a2:a10,"<>*")`

Countif Color

Unfortunately there is not an easy way to count cells with specific colors. To do this you will need to use VBA. Here’s a link with more information: CountIf Cell Color using VBA>.

Countif Duplicates

There are numerous ways to count duplicates, but let’s take a look at one of the easier methods, using only COUNTIF functions.

Picture

First, create a column to count how often a record appears on the data. Any record appearing more than once (>1) is considered a duplicate.
`=count`

Then we create a COUNTIF function to count the number of records that appear more than once:
`=count`

Countif with Two or Multiple Conditions – The Countifs Function

So far we’ve worked only with the COUNTIF Function. The COUNTIF Function can only handle one criteria at a time. To COUNTIF with multiple criteria you need to use the COUNTIFS Function. COUNTIFS behaves exactly like COUNTIF. You just add extra criteria. Let’s take a look at some examples…

IMAGE

COUNTIFS – Greater Than and Less Than

Let’s do a COUNTIF where we check if a number falls within a range. The number must be greater than 0, but less than 100:

COUNTIFS – Date Range

Now let’s try it with dates. Find any dates within the range 1/1/2015 to 7/15/2015:

COUNTIFS – Or

So far we’ve only dealt with AND criteria. Ex: Greater than 0 AND less than 100. What doing a COUNTIFS with OR?

countif pivot table

How to do a Countif in Excel
Criteria for Countif

have hyperlinks at the top to the various sections
have links to his content on the formula page with # to link to different stuff

when you apply criteria treat it like text

image

x –

How to use the COUNTIF Function for Excel: 