Skip to main content
SheetTutor.
← All tasks↓ Sample workbook

Count records that meet several conditions

Read-only

Step-by-step guide · read-only · Selected F6

Loading your interactive spreadsheet…

Preparing the worksheet and formulas…

Data & tools
Task requirements

In F6, count rows matching the team in F2, status in F3 and maximum age in F4.

Follow the highlighted cells. Switch to Your turn to edit. Answer cell: F6. Browser calculations may differ from your Excel version.

Matching rows
Selection & formula search

Practice stays on this device. No worksheet data is uploaded.

Notes & examples
About this lesson
Count & summarize7 min·Growing

Count open tickets for one team within an age limit, without including rows that pass only one test.

Choose the result you need

Count a row only when its team is Blue, status is Open and age is at most 5. Four source rows meet all three tests, including the lowercase labels.

=COUNTIFS(A2:A9,F2,B2:B9,F3,C2:C9,"<="&F4)
Download this example

COUNTIFS combines its condition pairs with AND. Adding a new pair narrows the same-row population; it does not create an OR alternative. Criteria labels in this example contain no wildcards or operators.

All result formulas
F6
=COUNTIFS(A2:A9,F2,B2:B9,F3,C2:C9,"<="&F4)
Complete example · Support tickets. Quoted strings show their exact spaces and spelling. Result cells are calculated outputs.Scroll sideways to read every column.
RowABCEF
1"Team""Status""Age (days)""Condition""Value"
2"Blue""Open"2"Team""Blue"
3"Blue""Closed"1"Status""Open"
4"Gold""Open"2"Maximum age"5
5"Blue""Open"5——
6"Blue""Open"6"Matching count"4
7"blue""open"0——
8"Gold""Closed"4——
9"Blue""Open"3——

These are reference examples with matching downloads. The primary worksheet has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.

Open the primary practice

Microsoft function reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.

All worked examples

Each example uses the source data in its own downloadable workbook. Cell references below belong to that file. Opening an example leaves the primary practice and your progress unchanged.

Require every condition on one row

Count a row only when its team is Blue, status is Open and age is at most 5. Four source rows meet all three tests, including the lowercase labels.

=COUNTIFS(A2:A9,F2,B2:B9,F3,C2:C9,"<="&F4)

Enter in F6 in this example's workbook. The download contains the complete source and all result formulas.

F6
4

COUNTIFS combines its condition pairs with AND. Adding a new pair narrows the same-row population; it does not create an OR alternative. Criteria labels in this example contain no wildcards or operators.

Download this example's workbook
Allow either of two teams

Count the Blue/Open/age group and the Gold/Open/age group, then add them. These two team labels cannot match the same row, so no row is counted twice.

=COUNTIFS(A2:A9,"Blue",B2:B9,F3,C2:C9,"<="&F4)+COUNTIFS(A2:A9,"Gold",B2:B9,F3,C2:C9,"<="&F4)

Enter in F6 in this example's workbook. The download contains the complete source and all result formulas.

F6
5

Adding counts implements this OR only because the alternatives are disjoint. Overlapping conditions need duplicate-row handling. Both branches retain the same status and age conditions.

Download this example's workbook
See why two team tests return zero

Reusing A2:A9 with Blue and Gold asks each cell to equal both labels at once. No source row can pass; zero is the correct result for that question.

=COUNTIFS(A2:A9,"Blue",A2:A9,"Gold")

Enter in F6 in this example's workbook. The download contains the complete source and all result formulas.

F6
0

This is a deliberate counterexample to OR. Repeating one criteria range is valid syntax, but both conditions still have to be true on the same row.

Download this example's workbook
Count an inclusive age interval

A cell can be at least 2 and at most 5 simultaneously. Repeating C2:C9 is useful here: five ages fall inside the inclusive interval.

=COUNTIFS(C2:C9,">=2",C2:C9,"<=5")

Enter in F6 in this example's workbook. The download contains the complete source and all result formulas.

F6
5

The bounds apply to the same numeric age. This example counts every team and status; the F2:F4 controls from the primary example are not used by this interval formula.

Download this example's workbook
UNDERSTAND THE IDEA

How it works

COUNTIF or COUNTIFS?

COUNTIF applies one criterion to one range. COUNTIFS is useful here because team, status and age must all match on the same record. It counts records; it does not add the age values.

AND is different from OR

This task asks for the selected team AND the selected status AND an age within the limit. Adding separate COUNTIF results would include partial matches and might count some rows more than once.

Data rules for this practice

Team and status comparisons ignore letter case. Ticket ages are numeric, nonnegative whole days. A zero-day ticket is valid and qualifies when the maximum is zero or higher. A team with no qualifying records returns zero.

Read the full step-by-step walkthrough
  1. Define one record

    Each row describes a single ticket. Count matching rows, not the number of cells containing a word.

  2. Choose the team

    Compare the Team column with the value in F2.

    A2:A9,F2
  3. Require the same status

    The status condition must be true on the same row as the team condition.

    B2:B9,F3
  4. Include the age boundary

    Join <= to F4 so the limit remains editable. An age exactly equal to F4 qualifies.

    C2:C9,"<="&F4
  5. Count the rows that survive

    Each matching row contributes one, regardless of its age. Change the team or limit and check which rows remain.

WHEN SOMETHING LOOKS OFF

Things to watch

The result is too high

Adding individual COUNTIF totals implements a different rule and can double-count rows.

Boundary tickets disappear

Use <= rather than < when tickets exactly on the limit should count.

A number is hard-coded

Reference F4 so changing the maximum age also changes the answer.

About this example

Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.

Browser: testedExcel: not verifiedGoogle Sheets: not verified

Content version 3. Practice checks compare calculated results across different datasets; they do not certify every possible formula.

Reference material