Function Problems

Count Differences of One or Less with SUMPRODUCT in Excel

Rana GarciaRana Garcia Nov 18, 2026 869 views

Question details

Count rows where the difference between values in columns B and C is less than or equal to one.

Count Differences of One or Less with SUMPRODUCT in Excel
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.
Before you start

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.

Solution 1

Count absolute differences of one or less

Use this method when values should qualify regardless of which column is larger.

1
Select a result cell

Click an empty cell where you want the count to appear.

2
Enter the formula

Type =SUMPRODUCT(--(ABS(B2:B10-C2:C10)<=1)) and press Enter.

3
Check the ranges

In the formula bar, replace B2:B10 and C2:C10 with your corresponding ranges if your data covers different rows.

How the comparison works: ABS ignores the direction of the difference, so differences from -1 through 1 are counted. SUMPRODUCT normally works without Ctrl+Shift+Enter.
Count differences in WPS Spreadsheets

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. 1. Open your workbook: Open WPS Spreadsheets and select Open to choose your workbook.
  2. 2. Select the result cell: Click an empty cell outside the data ranges.
  3. 3. Enter the formula: Type =SUMPRODUCT(--(ABS(B2:B10-C2:C10)<=1)) and press Enter.
  4. 4. Match your data: Edit the two ranges in the formula bar so they cover the corresponding rows in your sheet.
Count differences in either direction with a single formulaOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.