### 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 –

## COUNTIF VBA Examples

You can also access the Excel COUNTIF Function from within VBA, using Application.WorksheetFunction.

Type:

`application.worksheetfunction.CountIf(Range, Criteria)` Assuming we have the data displayed above:

`WorksheetFunction.CountIf(Range("A1:A10"), ">60")`

Will return 4 , as there are four cells with values larger that 60

`WorksheetFunction.CountIf(Range("A1:A10"), "10")`

Will return 1 , as there is one cell with value equal to 10

`MsgBox WorksheetFunction.CountIf(Range("A1:A10"), "<>")`

Will return 10 , as all cells have values

`MsgBox WorksheetFunction.CountIf(Range("A1:A10"), "")`

Will return 10 , as there are no blank cells Assuming we have the data in a table named “Table1”, like displayed above:

`WorksheetFunction.CountIf(Range("Table1"), "*test*")`

Will return 6, as there are six cells that contain the word “test”.

`WorksheetFunction.CountIf(Range("Table1"), "test")`

Will return 6, as there are no cells that contain ONLY the word “test”.

`WorksheetFunction.CountIf(Range("Table1"), "F*")`

Will return 6, as there are three cells whose values start with the letter “F”.