Excel COUNTIFS Formula to Count Cells Between 1,700 and 2,200
Question details
Count how many cells contain numeric values from 1,700 through 2,200, including both boundary values.
- Product
- Microsoft Excel COUNTIFS function
- Device & OS
- not provided
- Scenario
- The user needs to evaluate one cell range and count all values that fall within a specified numeric interval.
- Observed behavior
- The desired result is a count that includes every numeric value greater than or equal to 1,700 and less than or equal to 2,200.
Confirm that the cells in the range contain real numeric values rather than numbers stored as text. Replace the example range with the actual cells you want to evaluate, such as A2:A100.
Use COUNTIFS with inclusive minimum and maximum criteria
Use this formula when you need to count values in one Excel range from 1,700 to 2,200, including both numbers.
COUNTIFS evaluates the same range against two conditions. The >= criterion sets the minimum value, and the <= criterion sets the maximum value.
Click the blank cell where you want Excel to display the count.
Type =COUNTIFS(A2:A100,">=1700",A2:A100,"<=2200"), replacing A2:A100 with the range to evaluate.
Press Enter to count all numeric cells from 1,700 through 2,200 inclusively.
Use cell references for a changeable number range
Use this version when the lower and upper limits may change and you want to update the count without editing the formula.
Count Number Ranges Easily with COUNTIFS in WPS Spreadsheets
WPS Spreadsheets supports the COUNTIFS function, so you can count values between two numbers with the same inclusive criteria used in Excel. Enter the formula in a result cell, specify the data range twice, and apply lower and upper limits.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the workbook containing the values to count.
- 2. Choose an output cell: Click an empty cell where you want the calculated count to appear.
- 3. Enter the inclusive COUNTIFS formula: Type =COUNTIFS(A2:A100,">=1700",A2:A100,"<=2200"), replacing A2:A100 with your actual data range.
- 4. Apply the formula: Press Enter to display the number of cells whose values are between 1,700 and 2,200.
Frequently Asked Questions
Does COUNTIFS include 1,700 and 2,200 in the count?
Yes. Use >=1700 for the lower criterion and <=2200 for the upper criterion: =COUNTIFS(A2:A100,">=1700",A2:A100,"<=2200").
Why does my COUNTIFS formula return zero when values appear to be in range?
Check whether the source values are stored as text. Select the range and convert text numbers to numbers, then recalculate the formula.
Can I use different cells for the lower and upper limits?
Yes. If D2 contains the lower limit and E2 contains the upper limit, use =COUNTIFS(A2:A100,">="&D2,A2:A100,"<="&E2).
Can COUNTIFS count values between two numbers in more than one column?
Yes, but each criteria range in one COUNTIFS formula must have the same dimensions. Use matching ranges, such as A2:A100 and B2:B100, for separate conditions.
