Google Sheets COUNTIF Between Two Values: A Clear Guide

To count numbers between two limits in Google Sheets, use COUNTIFS with the same range twice: if the values are in A2:A100 and the limits are 10 and 20, enter =COUNTIFS(A2:A100,">=10",A2:A100,"<=20"). This counts values from 10 through 20, including both endpoints; change the operators if you want to exclude either limit.

Why COUNTIFS is used for a range

COUNTIF accepts one condition, such as “at least 10” or “equal to 20.” A between test has two conditions: a value must be at or above the lower limit and at or below the upper limit at the same time. COUNTIFS is designed to count cells that meet multiple conditions, so it is the straightforward choice for this task.

The name can be confusing because people often search for “COUNTIF between two values.” In practice, use the plural function COUNTIFS when you need both a lower and an upper test. The two tests apply to the same cells, and a cell is counted only when it satisfies both. This is an AND relationship, not an either-or test.

The formula counts each qualifying cell once, including repeated values. It does not count distinct values. For example, if the number 15 appears three times within the selected range, all three cells contribute to the result.

A basic example you can check

Suppose A2:A10 contains 8, 10, 12, 17, 20, 21, a blank cell, 15, and the text “n/a.” With those values in A2:A10, this formula checks the inclusive interval from 10 to 20: =COUNTIFS(A2:A10,">=10",A2:A10,"<=20"). The result is 5: the cells containing 10, 12, 17, 20, and 15 meet both conditions.

The blank and text entry do not add to this numeric count. This makes the formula useful for a list that may contain occasional empty cells or labels, though text-formatted numbers can require cleanup. The endpoints matter: 10 and 20 are included because the criteria use greater than or equal to and less than or equal to.

To try it in Sheets, put the sample values in column A and paste the formula into another cell. Keep the formula outside the range it counts; otherwise, the output cell may become part of the data range and affect the result if you later enter a value there.

How the criteria work

Each criterion is made from an operator and a value. Google Sheets expects comparison operators such as >= and <= to be part of a text criterion, so they appear inside quotation marks. In the basic example, ">=10" means “at least 10,” while "<=20" means “no more than 20.”

Because both criteria refer to A2:A100, Sheets checks each cell in that range against both tests. The two ranges supplied to COUNTIFS must have matching dimensions. Repeating A2:A100 is safe; pairing A2:A100 with a differently sized range can cause an error or an unintended comparison.

For clarity and easier editing, you can put the bounds in cells instead of typing them directly into the formula. With the values in A2:A100, the lower limit in C1, and the upper limit in D1, use =COUNTIFS(A2:A100,">="&C1,A2:A100,"<="&D1). The ampersand joins the comparison operator to the value in each limit cell. If you change C1 or D1, the count updates without changing the formula.

Include or exclude the endpoints

Choose the operators according to what “between” means for your task. In everyday conversation, between sometimes includes the limits and sometimes does not; the formula makes that choice explicit.

Include both limits: use >= for the lower limit and <= for the upper limit.
Exclude both limits: use > and <.
Include only the lower limit: use >= and <.
Include only the upper limit: use > and <=.

With values in A2:A100, a lower limit of 10 and an upper limit of 20, the strict version is =COUNTIFS(A2:A100,">10",A2:A100,"<20"). It omits 10 and 20. When the bounds are in C1 and D1, the same operator changes can be made in the cell-based formula by replacing the comparison strings.

Use cell references for changing limits

Bounds in separate cells are especially useful for reports, dashboards, and repeated analysis. Label C1 “Minimum” and D1 “Maximum,” then use the cell-reference formula above. This makes the criteria visible to other spreadsheet users and reduces the chance of overlooking a number embedded in a long formula.

For example, with numbers in B2:B50, the minimum in E2, and the maximum in F2, use =COUNTIFS(B2:B50,">="&E2,B2:B50,"<="&F2). If E2 is 5 and F2 is 15, the formula counts values from 5 through 15. If the lower limit is greater than the upper limit, no value can satisfy both conditions, so the result is zero. You may want to label the cells clearly or validate the limits to catch that input mistake.

Do not put quotation marks around a cell reference itself. The operator needs to be text, while the bound should come from the referenced cell. Joining them with & creates the criterion Sheets needs. If a bound cell is blank, the comparison may not mean what you intend, so check that both limit cells contain valid values.

Counting dates within a period

Dates in Google Sheets are stored as numeric date values, so the same two-condition approach works for dates. Assume event dates are in A2:A100, the start date is in C1, and the end date is in D1. To include both dates, use =COUNTIFS(A2:A100,">="&C1,A2:A100,"<="&D1). Format C1 and D1 as dates if you want them to display as calendar dates.

For a fixed date range, with event dates in A2:A100 and a period from January 1 through January 31, 2026, one option is =COUNTIFS(A2:A100,">="&DATE(2026,1,1),A2:A100,"<="&DATE(2026,1,31)). The DATE function avoids ambiguity between date formats such as month/day/year and day/month/year. In this example, it counts dates on or after January 1 and on or before January 31.

A subtle issue arises when the cells contain timestamps, not dates set to midnight. A timestamp on the final day, such as January 31 at 3 p.m., is later than January 31 at midnight, so a less-than-or-equal test against the end date may omit it. If D1 contains the final calendar date and A2:A100 contains timestamps, use a next-day exclusive boundary: =COUNTIFS(A2:A100,">="&C1,A2:A100,"<"&(D1+1)). This includes every time on D1 while excluding midnight at the start of the following day.

Common problems and how to fix them

If a result is unexpectedly zero or too small, check the data and criteria before rewriting the formula. These are frequent causes:

Numbers stored as text: A cell that looks like 12 may actually contain text. Convert the entries to numbers, for example by selecting the cells and using Format > Number, then checking whether the underlying values are numeric. Formatting alone may not convert text in every case; importing or pasting data can leave text values unchanged.
Wrong boundary operator: A strict greater-than test excludes values equal to the lower bound. Confirm whether endpoints should count and use the corresponding operators.
Different range sizes: In COUNTIFS, keep the criteria ranges aligned and the same size. For a single numeric range, repeat exactly the same range for each condition.
Dates interpreted differently: Check that the cells contain date values rather than text that resembles a date. Using DATE(year,month,day) in a fixed-date criterion avoids relying on how a typed date is interpreted.
Hidden spaces or unexpected entries: Spaces and text labels can make data behave differently from expected. Inspect suspicious cells, and clean imported data if needed.

Also verify the range endpoints. A formula using A2:A100 will not include a new value entered in A101. You can choose a suitably large range or use an open-ended column range, but avoid including the formula cell itself in the counted range if that could alter the result. For a large spreadsheet, a bounded range can be more efficient and easier to audit.

Google Sheets uses a spreadsheet locale that can affect formula punctuation. If the formula parser rejects commas, check File > Settings for the spreadsheet's locale; some locales use semicolons as function argument separators. The examples here use commas. Function names and the precise date display can also depend on spreadsheet settings, so distinguish a display-format issue from an actual text-versus-date issue.

COUNTIF, COUNTIFS, and other approaches

Use COUNTIF when there is only one condition, such as counting values greater than 10. Use COUNTIFS for a lower and upper bound, or whenever the count must satisfy multiple criteria. Google Sheets has both functions; using COUNTIF twice and combining the results is usually not the right substitute, because values satisfying both tests must be counted as an intersection rather than added together.

If you want to inspect the matching records rather than get only a count, a filtering approach may be more helpful. For instance, the Sheets FILTER function can return rows meeting conditions, while COUNTIFS returns a single count. The best choice depends on the desired output: use a count for a summary figure, and a filter when you need to review or work with the underlying entries. For a count based on multiple columns, add additional criteria range-and-condition pairs to COUNTIFS, keeping each range aligned by row.

Excel also has a COUNTIFS function with similar syntax, but this page describes Google Sheets in a browser. Menu locations, locale settings, and some spreadsheet features can differ between products. If a file is shared with Excel users, basic COUNTIFS formulas are commonly compatible, but confirm behavior when the workbook also uses product-specific functions or settings.

Frequently Asked Questions

Can I use COUNTIF to count between two numbers?
Use COUNTIFS for two simultaneous limits. COUNTIF handles one condition; the lower and upper tests both need to be true for a value to qualify.

Does COUNTIFS include the minimum and maximum?
It includes them only if the criteria use >= and <=. Use > or < for a boundary you want to exclude.

How do I reference the two limits from cells?
Join each comparison operator to its bound cell with &. For example, with values in A2:A100 and bounds in C1 and D1, use =COUNTIFS(A2:A100,">="&C1,A2:A100,"<="&D1).

Can COUNTIFS count dates between two dates?
Yes. Dates are comparable values in Sheets, so use the same inclusive lower- and upper-bound tests. If the range contains timestamps and the final date must include all times that day, use a less-than test against the day after the end date.

Why does my formula return zero?
Check that the bounds are in the intended order, the data range includes the relevant rows, and the entries are real numbers or dates rather than text. Also confirm the endpoint operators and, if the formula gives a parse error, whether your locale expects semicolons instead of commas.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.