COUNTIFS practice in Google Sheets

COUNTIFS counts the rows where every condition holds. It takes pairs of range and criterion, in Google Sheets as in Excel, and every range must be the same size.

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

Questions

Can COUNTIFS count between two numbers in Google Sheets?

Yes. Use the same range twice with two criteria, such as =COUNTIFS(B2:B10,">=10",B2:B10,"<=20"), exactly as in Excel.

Can COUNTIFS use data from another sheet in Google Sheets?

Yes, as long as the data is in the same spreadsheet. Put the sheet name and ! in front of the range, as in =COUNTIFS(Orders!A2:A100,"North"). If the name has spaces, put it in single quotes. All the ranges must still be the same size, even when they come from the same sheet.