Hands-On Excel

Hands-On Excel Challenges COUNTIFS

Excel COUNTIFS practice

=COUNTIFS(B2:B13, "North", C2:C13, "Shipped") counts the rows where the region is North and the status is Shipped. Every condition has to be true on the same row.

CoreAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

15 · COUNTIFS · Core

Count with two conditions

In F2, count the North orders that are Shipped. In F3, count the shipped orders from North or South.

COUNTIFS(range 1, condition 1, range 2, condition 2) counts the rows where every condition is true. For "this or that" in the same column, add two counts together.

orders.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · COUNTIFS

Syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

The arguments come in pairs: a range, then what to look for in it. A row is counted only when every pair matches. All the ranges must have the same number of rows.

A worked example

Twelve orders, with the region in column B and the status in column C. Five orders are from North, and seven are Shipped. Only the rows where both are true count: orders 1001, 1006 and 1012, so =COUNTIFS(B2:B13, "North", C2:C13, "Shipped") returns 3.

"North or South" needs two counts

It's tempting to write both regions into one COUNTIFS:

=COUNTIFS(B2:B13, "North", B2:B13, "South", C2:C13, "Shipped")

That asks for orders that are in North and South at the same time. There are none, so the answer is 0. For "either", count each region and add the results:

=COUNTIFS(B2:B13, "North", C2:C13, "Shipped")
  + COUNTIFS(B2:B13, "South", C2:C13, "Shipped")

Two conditions on the same column can work together when they describe a range of numbers: =COUNTIFS(D2:D13, ">=100", D2:D13, "<200") counts values from 100 up to, but not including, 200.

COUNTIF or COUNTIFS?

COUNTIF takes exactly one range and one condition. COUNTIFS takes one or more pairs, so it does everything COUNTIF does. SUMIFS works the same way, with the range to add in front.

Common mistakes

FAQ

How do I use COUNTIFS with multiple criteria?
Give it a range and a condition for each criterion: =COUNTIFS(B2:B13,"North",C2:C13,"Shipped") counts the rows where both match.
How do I count with OR in COUNTIFS?
Add one COUNTIFS for each option: =COUNTIFS(B2:B13,"North",C2:C13,"Shipped")+COUNTIFS(B2:B13,"South",C2:C13,"Shipped").
Why does my COUNTIFS return 0?
No row matches every condition at once. Two different values for the same column always give 0. Also check the spelling of each condition and extra spaces in the data.
How do I count values between two numbers?
Use the same range twice: =COUNTIFS(D2:D13,">=100",D2:D13,"<200").

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Lock just the row or the columnMixed references Advanced · 3 min
  6. 06 Percent of a totalPercentages Starter · 3 min
  7. 07 Up or down by how much?Percent change Core · 3 min
  8. 08 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  9. 09 Pass or fail?IF Core · 3 min
  10. 10 Both or either?AND, OR Core · 3 min
  11. 11 Grade bands with nested IFNested IF Core · 3 min
  12. 12 Count the ones that matchCOUNTIF Core · 3 min
  13. 13 Add up the ones that matchSUMIF Core · 3 min
  14. 14 Use a cell as the conditionCriteria in a cell Core · 3 min
  15. 15 Count with two conditionsCOUNTIFS Core · 3 min
  16. 16 Add up with two conditionsSUMIFS Core · 3 min
  17. 17 Look it upVLOOKUP Core · 3 min
  18. 18 Find the right bracketVLOOKUP approximate match Core · 3 min
  19. 19 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  20. 20 Where is it in the list?MATCH Core · 3 min
  21. 21 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  22. 22 Catch the error, not the bugIFERROR Core · 3 min
  23. 23 Split first and last namesLEFT, MID + FIND Core · 3 min
  24. 24 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min
  25. 25 Mistake hunt: hard-coded numbersReferences instead of typed numbers Core · 3 min
  26. 26 Mistake hunt: numbers stored as textNumbers stored as text Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy