COUNTIFS practice exercises

Count rows that meet several conditions at once, including a number between two limits.

Start the first exercise

How COUNTIFS works

COUNTIFS counts the rows where every one of your conditions is met. It takes pairs of arguments: a range to test and a criterion, repeated for each condition. It works the same in Excel and Google Sheets.

=COUNTIFS(A2:A9,"North",C2:C9,">400") counts the North orders with sales over 400. To count numbers between two limits, use the same range twice: =COUNTIFS(B2:B8,">=18",B2:B8,"<=30") counts the people aged 18 to 30, both ages included. Put a limit in a cell by joining it to the operator, as in ">="&F2.

Unlike SUMIFS there is no sum range, so the result is always a count of rows. All the ranges must be the same size. For a single condition COUNTIF is shorter. The exercises below cover two conditions, counting between two numbers, counting orders between two dates, and conditions taken from cells.

  1. 1COUNTIFS with two conditionsIn E2, use COUNTIFS to count the North orders with Sales over 400.
  2. 2Count between two numbers with COUNTIFSPremiumIn E2, use COUNTIFS to count the people in B2:B8 aged 18 to 30, both ages included. Use the same range twice, once for each limit.
  3. 3Count the orders between two datesPremiumIn F3, count the orders dated from F1 to F2, both days included. Use the date column twice in COUNTIFS and join each operator to its cell: ">="&F1 and "<="&F2.
  4. 4COUNTIFS with the conditions in cellsPremiumIn F3, count the reps from the region in F1 with sales of at least the amount in F2. Point at both cells: F1 as it is, and F2 joined to ">=".

Functions in this topic

Read the guide

Questions

How do I use COUNTIFS with OR logic?

COUNTIFS only combines conditions with AND. For OR, add one COUNTIFS per option, as in =COUNTIFS(A2:A9,"North")+COUNTIFS(A2:A9,"South"). That is fine when a row can match only one of them. If a row can match both, subtract the rows that match both, or it will be counted twice.

How do I count cells that contain certain text?

Put wildcards around the word, as in =COUNTIFS(A2:A9,"*desk*"). The * stands for any number of characters, so it counts "Desk lamp" and "Standing desk". The match ignores upper and lower case. Add more range and criterion pairs to count only the rows that also meet other conditions.

Why does COUNTIFS return 0 when I can see matches?

A common cause is a cell reference inside the quotes, such as ">=F2". Excel then compares with the text "F2". Write ">="&F2 instead. Other causes are extra spaces in the data, numbers stored as text, and dates that are text. Check one condition at a time to find the culprit.