Count Differences of One or Less with SUMPRODUCT in Excel
Question details
Count rows where the difference between values in columns B and C is less than or equal to one.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Compare corresponding values in two columns and count matching rows using SUMPRODUCT.
- Observed behavior
- The desired count should reflect either an absolute difference in both directions or a one-direction subtraction.
Check that columns B and C contain numeric values and that the two ranges cover the same rows. The examples use rows 2 through 10; adjust both ranges to match your data.
Count absolute differences of one or less
Use this method when values should qualify regardless of which column is larger.
Click an empty cell where you want the count to appear.
Type =SUMPRODUCT(--(ABS(B2:B10-C2:C10)<=1)) and press Enter.
In the formula bar, replace B2:B10 and C2:C10 with your corresponding ranges if your data covers different rows.
Count one-direction differences of one or less
Use this method when you specifically want to test whether column B minus column C is less than or equal to one.
Count matching differences with WPS Spreadsheets
Use SUMPRODUCT and ABS in WPS Spreadsheets to count rows where two numeric values differ by no more than one.
- 1. Open your workbook: Open WPS Spreadsheets and select Open to choose your workbook.
- 2. Select the result cell: Click an empty cell outside the data ranges.
- 3. Enter the formula: Type =SUMPRODUCT(--(ABS(B2:B10-C2:C10)<=1)) and press Enter.
- 4. Match your data: Edit the two ranges in the formula bar so they cover the corresponding rows in your sheet.

Frequently Asked Questions
What does ABS do in the SUMPRODUCT formula?
ABS converts each difference to its absolute value. For example, both -1 and 1 qualify when you use ABS(B2:B10-C2:C10)<=1.
Do I need to press Ctrl+Shift+Enter?
Normally, no. Enter the SUMPRODUCT formula in a cell and press Enter, including in older versions of Excel.
Why does the formula without ABS count differences below -1?
The test B2:B10-C2:C10<=1 includes all negative results. Add ABS around the subtraction if you only want differences from -1 through 1.
Can blank cells affect the count?
Yes. Blank cells can be treated as zero in the subtraction, so check the input ranges and fill in or remove incomplete rows before using the formula.




