Calculation Issues

How to Count Number Pairs in the Same Row in Excel and WPS

Jason MillerJason Miller Nov 10, 2026 869 views

Question details

The user needs to calculate the frequency of specific number pairs appearing together in the same row within a dataset and output a combination matrix.

How to Count Number Pairs Occurring in the Same Row
Product
Spreadsheet
Device & OS
not provided
Scenario
Analyzing a weekly source table to find the co-occurrence of number pairs across rows and generating a summary matrix.
Observed behavior
A dynamic combination matrix is generated showing the count of each number pair's co-occurrence in the data rows.
Before you start

Ensure your dataset is organized in clean rows and columns without merged cells. If you plan to use dynamic array functions like MAKEARRAY or BYROW, verify that your spreadsheet software is updated to a version that supports them.

Solution 1Recommended

Use Dynamic Arrays (MAKEARRAY and BYROW) for an Automatic Matrix

Modern spreadsheet functions can dynamically generate the entire matrix, automatically checking each row for both numbers.

This dynamic array solution produces the complete combination matrix, including row and column labels, by evaluating the maximum value in your grid and sequentially iterating through the rows.

1
Define your data range

Identify the data range containing your numbers. For this formula to work properly, assume your data is in the named range or specific cells (e.g., A2:E10, which we will refer to as 'grid').

2
Enter the formula

Select the empty top-left cell where you want your combination matrix to start. Type the following formula: =LET(k, MAX(grid), seq, SEQUENCE(k), CountCombin, LAMBDA(r, c, LET(i, INDEX(seq, r - 1), j, INDEX(seq, c - 1), check_weeks, BYROW(grid, LAMBDA(each_week, AND(OR(each_week = i), OR(each_week = j)))), IF(AND(r = 1, c = 1),"",IF(r = c,"X",IF(AND(r > 1, c = 1),i,IF(AND(r = 1, c > 1),j,SUM(N(check_weeks)))))))), MAKEARRAY(k + 1, k + 1, CountCombin))

3
Generate the matrix

Press Enter. The dynamic arrays will calculate the co-occurrence counts and automatically spill to fill out the newly created combination matrix.

Use Dynamic Arrays (MAKEARRAY and BYROW) for an Automatic Matrix
Dynamic Spill Range: Ensure there is enough empty space below and to the right of your formula cell; otherwise, you will encounter a #SPILL! error.
Advanced Spreadsheet Features

Analyze Data and Build Matrices with WPS Spreadsheet

WPS Spreadsheet fully supports advanced mathematical functions, logical operators, and dynamic array calculations. You can easily calculate combination matrices and frequency counts using identical formulas to those in Microsoft Excel.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your weekly source table.
  2. 2. Select the destination: Click an empty cell where you want the new combination matrix to begin spilling.
  3. 3. Paste the formula: Paste the provided dynamic LET/LAMBDA or MAKEARRAY array formula and press Enter to instantly see your pair counts.
Fully compatible with Microsoft Excel formulas and .xlsx format files.Supports advanced arrays and LAMBDA functions for complex data analysis.Lightweight, fast, and completely free to use for everyday office tasks.
microsoft office alternative - wps office

Frequently Asked Questions

What does a combination matrix do in this context?

A combination matrix maps all unique values from your data into headers for both rows and columns. The intersecting cells display the frequency of times those two numbers co-occur in the same row within your source table.

Why do I get a #NAME? error when using LAMBDA or MAKEARRAY?

The #NAME? error typically occurs if your current software version does not support newer dynamic array functions like LAMBDA, MAKEARRAY, or BYROW. To fix this, update to the latest version of your spreadsheet software that includes these advanced array calculations.

Can I count these pairs with traditional conditional formatting?

While conditional formatting can highlight cells containing specific numbers, it cannot calculate and build a summary matrix of co-occurrences. You must use array formulas or scripts to generate and compile the frequency counts.

How can I ensure blank cells are ignored when counting rows?

Array functions like PIVOTBY combined with filtering functions (e.g., using TOCOL with the IFS function checking for <>"") are designed to filter out blank cells before generating the counts, ensuring empty values do not skew your pair count.