The result is too high
Adding individual COUNTIF totals implements a different rule and can double-count rows.
Count records that meet several conditions
About this lesson
Count open tickets for one team within an age limit, without including rows that pass only one test.
Task requirements
In F6, count rows matching the team in F2, status in F3 and maximum age in F4.
Teaching · read-only worksheet ·
Step 1 / 5Define one record
More
Inspect the highlighted cells. This step has no automatic change to apply. Teaching is read-only; switch to Your turn to edit.
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Follow the highlighted cells. Switch to Your turn to edit. Answer cell: F6. Browser calculations may differ from your Excel version.
Practice stays on this device. No worksheet data is uploaded.
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)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)
| Row | A | B | C | E | F |
|---|---|---|---|---|---|
| 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 practiceMicrosoft reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.
All worked examples
Expand an example to read its complete source data and checked results, then download the matching workbook. Cell references belong to that example. Opening it 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.
| Row | A | B | C | E | F |
|---|---|---|---|---|---|
| 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 | — | — |
- 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 workbookAllow 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.
| Row | A | B | C | E | F |
|---|---|---|---|---|---|
| 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" | 5 |
| 7 | "blue" | "open" | 0 | — | — |
| 8 | "Gold" | "Closed" | 4 | — | — |
| 9 | "Blue" | "Open" | 3 | — | — |
- 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 workbookSee 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.
| Row | A | B | C | E | F |
|---|---|---|---|---|---|
| 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" | 0 |
| 7 | "blue" | "open" | 0 | — | — |
| 8 | "Gold" | "Closed" | 4 | — | — |
| 9 | "Blue" | "Open" | 3 | — | — |
- 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 workbookCount 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.
| Row | A | B | C | E | F |
|---|---|---|---|---|---|
| 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" | 5 |
| 7 | "blue" | "open" | 0 | — | — |
| 8 | "Gold" | "Closed" | 4 | — | — |
| 9 | "Blue" | "Open" | 3 | — | — |
- 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 workbookOverlapping OR: count once
At age 5 or less, five rows belong to Blue and five are Open. Four rows meet both tests. Adding 5 + 5 gives 10, counting those four twice; subtracting the intersection gives 6 distinct tickets. Worksheet rows 2, 3, 4, 5, 7 and 9 qualify. Row 6 is too old even though it meets both text conditions. The lowercase row still counts.
=COUNTIFS(A2:A9,F2,C2:C9,"<="&F4)+COUNTIFS(B2:B9,F3,C2:C9,"<="&F4)-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.
| Row | A | B | C | E | F |
|---|---|---|---|---|---|
| 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" | 6 |
| 7 | "blue" | "open" | 0 | — | — |
| 8 | "Gold" | "Closed" | 4 | — | — |
| 9 | "Blue" | "Open" | 3 | — | — |
- F6
- 6
Count (selected team OR selected status) AND age at most F4. Subtract rows matching both branches once; keep the age limit in all three counts. Labels here are nonempty plain text without wildcards or comparison operators, matched without regard to case. Ages are numeric nonnegative days. This OR question is separate from the primary AND practice.
Download this example's workbookOR with changed filters
With Gold, Open and a maximum age of 4, the two branches each count 4, and 3 rows match both. The distinct count is 4 + 4 - 3 = 5, from worksheet rows 3, 4, 5, 6 and 9. The four-day boundary is included; the five-day ticket is excluded. Use F2:F4 rather than hard-coding the first example.
=COUNTIFS(A2:A9,F2,C2:C9,"<="&F4)+COUNTIFS(B2:B9,F3,C2:C9,"<="&F4)-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.
| Row | A | B | C | E | F |
|---|---|---|---|---|---|
| 1 | "Team" | "Status" | "Age (days)" | "Condition" | "Value" |
| 2 | "Gold" | "Open" | 7 | "Team" | "Gold" |
| 3 | "Blue" | "Open" | 1 | "Status" | "Open" |
| 4 | "Gold" | "Closed" | 2 | "Maximum age" | 4 |
| 5 | "Gold" | "Open" | 0 | — | — |
| 6 | "gold" | "OPEN" | 4 | "Matching count" | 5 |
| 7 | "Gold" | "Open" | 5 | — | — |
| 8 | "Blue" | "Closed" | 2 | — | — |
| 9 | "Gold" | "Open" | 3 | — | — |
- F6
- 5
Count (selected team OR selected status) AND age at most F4. Subtract rows matching both branches once; keep the age limit in all three counts. Labels here are nonempty plain text without wildcards or comparison operators, matched without regard to case. Ages are numeric nonnegative days. This OR question is separate from the primary AND practice.
Download this example's workbookOR with an empty branch
No ticket belongs to the selected Green team, so its branch and the intersection both return numeric zero. Four Open tickets are within the five-day limit: worksheet rows 2, 4, 6 and 7. The OR count is 0 + 4 - 0 = 4. The primary AND question on this same data returns 0; the two questions intentionally have different answers.
=COUNTIFS(A2:A9,F2,C2:C9,"<="&F4)+COUNTIFS(B2:B9,F3,C2:C9,"<="&F4)-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.
| Row | A | B | C | E | F |
|---|---|---|---|---|---|
| 1 | "Team" | "Status" | "Age (days)" | "Condition" | "Value" |
| 2 | "Blue" | "Open" | 0 | "Team" | "Green" |
| 3 | "Blue" | "Closed" | 1 | "Status" | "Open" |
| 4 | "Gold" | "Open" | 2 | "Maximum age" | 5 |
| 5 | "Gold" | "Closed" | 3 | — | — |
| 6 | "Blue" | "Open" | 4 | "Matching count" | 4 |
| 7 | "Gold" | "Open" | 5 | — | — |
| 8 | "Blue" | "Open" | 6 | — | — |
| 9 | "Gold" | "Open" | 7 | — | — |
- F6
- 4
Count (selected team OR selected status) AND age at most F4. Subtract rows matching both branches once; keep the age limit in all three counts. Labels here are nonempty plain text without wildcards or comparison operators, matched without regard to case. Ages are numeric nonnegative days. This OR question is separate from the primary AND practice.
Download this example's workbookCount the full end date
Count Open tickets created from October 1 through October 2, including both calendar days. Rows 3, 4, 5 and 6 qualify, giving 4; the lowercase status still matches. G8 stops at October 2 midnight and returns only 3. G9 admits October 3 midnight and returns 5. Row 7 is inside the dates but Closed, so it does not count.
=COUNTIFS($C$2:$C$9,$G$2,$B$2:$B$9,">="&$G$3,$B$2:$B$9,"<"&($G$4+1))Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Recorded time (label)" | "Numeric timestamp" | "Status" | "Included? 1/0" | "Control / result" | "Value" |
| 2 | "2026-09-30 18:00" | 46295.75 | "Open" | 0 | "Status" | "Open" |
| 3 | "2026-10-01 00:00" | 46296 | "Open" | 1 | "Start date" | 46296 |
| 4 | "2026-10-01 12:00" | 46296.5 | "Open" | 1 | "End date (inclusive)" | 46297 |
| 5 | "2026-10-02 00:00" | 46297 | "Open" | 1 | — | — |
| 6 | "2026-10-02 18:00" | 46297.75 | "open" | 1 | — | — |
| 7 | "2026-10-02 12:00" | 46297.5 | "Closed" | 0 | "Full end date" | 4 |
| 8 | "2026-10-03 00:00" | 46298 | "Open" | 0 | "Midnight cutoff: too narrow" | 3 |
| 9 | "2026-10-03 12:00" | 46298.5 | "Open" | 0 | "Next midnight included: too wide" | 5 |
- G7
- 4
- G8
- 3
- G9
- 5
- D2
- 0
- D3
- 1
- D4
- 1
- D5
- 1
- D6
- 1
- D7
- 0
- D8
- 0
- D9
- 0
Count rows, not time values. Column B contains numeric Excel-1900 timestamps; column A is a reading label only. G3 and G4 must be whole calendar dates at midnight, with G3 no later than G4. Use >= start and < end+1 to include the entire final day; <= end stops at its midnight, while <= end+1 includes the next midnight. All timestamps share one assumed local calendar; no timezone or text-date conversion is performed. Status is nonempty plain text, matched without regard to case. This separate reference does not change the primary team/status/age AND practice. Native Excel and Google Sheets recalculation of this file remains unverified.
Download this example's workbookCount one calendar day
Both date controls now specify October 1. The intended interval still has a full day: rows 3 and 4 give 2. Stopping at the date itself includes only midnight, returning 1; admitting the following midnight includes row 5 and returns 3. The correct formula is unchanged, with the end date still a whole-date value.
=COUNTIFS($C$2:$C$9,$G$2,$B$2:$B$9,">="&$G$3,$B$2:$B$9,"<"&($G$4+1))Enter in G7 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | F | G |
|---|---|---|---|---|---|---|
| 1 | "Recorded time (label)" | "Numeric timestamp" | "Status" | "Included? 1/0" | "Control / result" | "Value" |
| 2 | "2026-09-30 18:00" | 46295.75 | "Open" | 0 | "Status" | "Open" |
| 3 | "2026-10-01 00:00" | 46296 | "Open" | 1 | "Start date" | 46296 |
| 4 | "2026-10-01 12:00" | 46296.5 | "Open" | 1 | "End date (inclusive)" | 46296 |
| 5 | "2026-10-02 00:00" | 46297 | "Open" | 0 | — | — |
| 6 | "2026-10-02 18:00" | 46297.75 | "open" | 0 | — | — |
| 7 | "2026-10-02 12:00" | 46297.5 | "Closed" | 0 | "Full end date" | 2 |
| 8 | "2026-10-03 00:00" | 46298 | "Open" | 0 | "Midnight cutoff: too narrow" | 1 |
| 9 | "2026-10-03 12:00" | 46298.5 | "Open" | 0 | "Next midnight included: too wide" | 3 |
- G7
- 2
- G8
- 1
- G9
- 3
- D2
- 0
- D3
- 1
- D4
- 1
- D5
- 0
- D6
- 0
- D7
- 0
- D8
- 0
- D9
- 0
Count rows, not time values. Column B contains numeric Excel-1900 timestamps; column A is a reading label only. G3 and G4 must be whole calendar dates at midnight, with G3 no later than G4. Use >= start and < end+1 to include the entire final day; <= end stops at its midnight, while <= end+1 includes the next midnight. All timestamps share one assumed local calendar; no timezone or text-date conversion is performed. Status is nonempty plain text, matched without regard to case. This separate reference does not change the primary team/status/age AND practice. Native Excel and Google Sheets recalculation of this file remains unverified.
Download this example's workbookHow 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
- Define one record
Each row describes a single ticket. Count matching rows, not the number of cells containing a word.
- Choose the team
Compare the Team column with the value in F2.
A2:A9,F2 - Require the same status
The status condition must be true on the same row as the team condition.
B2:B9,F3 - Include the age boundary
Join <= to F4 so the limit remains editable. An age exactly equal to F4 qualifies.
C2:C9,"<="&F4 - Count the rows that survive
Each matching row contributes one, regardless of its age. Change the team or limit and check which rows remain.
Things to watch
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.
Content version 3. Practice checks compare calculated results across different datasets; they do not certify every possible formula.