Scribd
Upload
61 views7 pages

Excel COUNTIFS Function

Uploaded by

sehrishb01
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
61 views7 pages

Excel COUNTIFS Function

Uploaded by

sehrishb01
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

Excel COUNTIFS Function

Summary 
The Excel COUNTIFS function returns the count of cells that meet one or more
criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other
conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for
partial matching.

Purpose 
Count cells that match multiple criteria

Return value 
The number of times criteria are met

Syntax 
=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

Arguments 
 range1 - The first range to evaulate.
 criteria1 - The criteria to use on range1.
 range2 - [optional] The second range to evaluate.
 criteria2 - [optional] The criteria to use on range2.

Usage notes 
COUNTIFS counts the number of cells in a range that match supplied criteria. Unlike
the COUNTIF function, COUNTIFS can apply more than one set of criteria, with
more than one range. Ranges and criteria are applied in pairs, and only the first pair is
required. For each additional criteria, you must supply another range/criteria pairs. Up
to 127 range/criteria pairs are allowed.
Examples
With the example shown, COUNTIFS can be used to count records using 2 criteria as
follows:
=COUNTIFS(C5:C14,"red",D5:D14,"TX") // red and TX
=COUNTIFS(C5:C14,"red",F5:F14,">20") // red and >20
Notes:

 Each additional range must have the same number of rows and columns as
range1, but ranges do not need to be adjacent. If you supply ranges with a mismatch,
you'll get a #VALUE error.
 Non-numeric criteria needs to be enclosed in double quotes but numeric criteria
does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a
number).
 The wildcard characters ? and * can be used in criteria. A question mark
matches any one character and an asterisk matches any sequence of characters.
 To find a literal question mark or asterisk, use a tilde (~) in front question mark
or asterisk (i.e. ~?, ~*).

COUNTIFS formula examples

Customer is new
To mark a customer as new in a list or table, you can use the COUNTIFS function and
an expanding range in a helper column. In the example shown, the formula in E5,
copied down, is: =(COUNTIFS($B$5:B5,B5)=1)+0 The...

Count numbers by range with COUNTIFS


To count numeric data by range or grouping, you can build a summary table and use
COUNTIFS to count values at each threshold. In the example show, we have a list of
names and ages and are using the COUNTIFs function to...

Count cells that contain text


To count the number of cells that contain text (i.e. not numbers, not errors, not blank),
use the COUNTIF function and a wildcard. In the generic form of the formula
(above), rng is a range of cells, and "*" is a...

Count if two criteria match


If you want to count rows where two (or more) criteria match, you can use a formula
based on the COUNTIFS function. In the example shown, we want to count the
number of orders with a color of "blue" and a quantity > 15...

Count cells between two numbers


To count the number of cells that contain values between two numbers in a range of
cells, you can use the COUNTIFS function. In the generic form of the formula
(above), range represents a range of cells that contain...

Count paired items in listed combinations


To build a summary table with a count of paired items that appear in a list of existing
combinations, you can use a helper column and a formula based on the COUNTIFS
function. In the example shown the formula in cell H5...

Two-way summary count with COUNTIFS


To build a two-way summary count (i.e. summarizing by rows and columns) you can
use the COUNTIFS function. In the example shown, the formula in G5 is:
=COUNTIFS(dept,$F5,class,G$4) How this formula works The...

Sum time over 30 minutes


To sum the total amount of time over 30 minutes, given a set of times that represent
duration, you can use the SUMPRODUCT and TIME functions. In the example
shown, the formula in G5 is: =SUMPRODUCT((times-TIME(0,30,0...

Course completion summary with criteria


This example illustrates one way to summarize course completion data using a
criteria, in this case "group". In the example shown, the formula in I4 is:
=COUNTIFS($B$4:$B$11,$H4,D$4:D$11,"<>")/COUNTIFS...

Summary count of non-blank categories


To build a summary count of non-blank categories, you can use the COUNTIFS
function. In the example show, the formula in G6 is:
=COUNTIFS($B$5:$B$11,F6,$C$5:$C$11,"<>") How this formula works This...

Course completion status summary


To build a summary to show course completion status based on a data log, you can
use the COUNTIFS function together with the IF function. In the example shown, the
formula in G4 is: =IF(COUNTIFS(name,$F4,course,G$3...

Highlight duplicate rows


Excel contains a built-in preset for highlighting duplicate values with conditional
formatting, but it only works at the cell level. If you want to highlight entire rows that
are duplicates you'll need to use your own...

COUNTIFS with variable range


To configure COUNTIFS (or COUNTIF) with a variable range, you can use the
OFFSET function. In the example shown, the formula in B11 is:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>") This formula...

Rank if formula
To rank items in a list using one or more criteria, you can use the COUNTIFS
function. In the example shown, the formula in E5 is:
=COUNTIFS(groups,C5,scores,">"&D5)+1 where "groups" is the named...

COUNTIFS with variable table column


To use COUNTIFS with a variable table column, you can use INDEX and MATCH to
find and retrieve the column for COUNTIFS. In the example shown, the formula in
H5 is: =COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0...

Related videos

How to use the COUNTIFS function


In this video, we'll look at how to use the COUNTIFS function to count cells that
meet a multiple criteria in a set of data.
How to build a simple summary table
Pivot Tables are fantastic tools for summarizing data, but you can also use formulas to
build your own summaries using functions like COUNTIF and SUMIF. See how in
this 3 minute video.

Related functions 

Excel COUNTIF Function


COUNTIF is a function to count cells that meet a single criterion. COUNTIF can be
used to count cells with dates, numbers, and text that meet specific criteria. The
COUNTIF function supports logical operators (>,...

Excel Formula Training


Formulas are the key to getting things done in Excel. In this accelerated training,
you'll learn how to use formulas to manipulate text, work with dates and times, lookup
values with VLOOKUP and INDEX & MATCH, count and sum with criteria,
dynamically rank values, and create dynamic ranges. You'll also learn how to
troubleshoot, trace errors, and fix problems. Instant access. See details here.

You might also like