Formula Errors

How to Use an Excel Formula to Add Points When Cell Pairs Match

Camila Milosovich Oct 10, 2026 870 views

Question details

The user needs a formula to compare three specific pairs of cells (E3 with H3, F3 with I3, and G3 with J3) and add specific point values (5, 3, and 1 respectively) when each pair matches.

How to Create an Excel Formula to Add Points for Matching Cell Pairs

Product: Excel
Device & OS: not provided
Scenario: Creating a scoring or evaluation system based on whether corresponding cells in different columns contain matching data, with a maximum possible total score of 9.
Observed behavior: Requires a logical function or arithmetic solution that correctly evaluates the cell pairs and adds up the corresponding weights without returning errors.

Before you start

Ensure that the cells you are comparing are formatted identically (e.g., both as Text or both as Numbers) so that the formula correctly recognizes exact matches without being thrown off by hidden spaces or mismatched formats.

Solution 1Recommended

Use the IF and SUM Functions with Conditional Multipliers

This method accurately calculates points for matching pairs while gracefully handling completely blank rows to avoid cluttering your spreadsheet with zeros.

In spreadsheet logic, a TRUE statement is evaluated as the number 1, and a FALSE statement is evaluated as 0. By multiplying a comparison by a specific number, you can assign points only when the cells match.

Select the destination cell

Click on the cell where you want the total score to appear, such as cell B3.

Enter the comprehensive formula

Type or paste the following formula into the formula bar: =IF(COUNT(E3:J3)=0,"",SUM((E3=H3)*5,(F3=I3)*3,(G3=J3)))

Apply the formula

Press Enter to execute the calculation. The cell will remain blank if the target range is empty, or it will display the total points (up to 9) for matching pairs.

Use the IF and SUM Functions with Conditional Multipliers

Formula Breakdown: The expression (E3=H3) outputs TRUE (1) if the cells match. Multiplying it by 5 gives you 5 points. The SUM function then adds all the boolean math results together.

Solution 2

Use a Shorter Arithmetic Formula

A more concise formula using basic arithmetic operations, ideal if you do not need to account for empty cells.

Fast and Easy Data Analysis

Easily Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas and boolean math operations. You can implement custom scoring systems with a familiar, easy-to-use interface without needing complex scripting.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing your cell pairs.
2. Select the score cell: Click on the target cell where the points will be totaled.
3. Input the formula: Type the boolean math formula =5*(E3=H3)+3*(F3=I3)+(G3=J3) into the formula bar and press Enter.
4. Apply to multiple rows: Click the small square at the bottom right corner of the cell and drag it down to apply the scoring logic to the rest of your data.

Easily calculate custom points and execute conditional logic formulas.100% compatible with Microsoft Excel (.xlsx, .xls) files and standard functions.Built-in formula error checking to ensure data accuracy.Lightweight software with fast processing for extensive data sets.

Frequently Asked Questions

Why is my formula returning a #VALUE! error?

This usually happens if one of the referenced cells contains an error or text that cannot be evaluated mathematically in this specific context. Ensure your data doesn't contain hidden formatting issues or error codes inherited from other formulas.

Can I use this formula to compare text strings as well as numbers?

Yes, the boolean comparison (e.g., E3=H3) works perfectly for both text strings and numerical values. However, keep in mind that standard text comparisons in this formula are not case-sensitive.

How do I modify the formula if I have more than three pairs to compare?

You can easily expand the formula by adding more logical components. For example, to include a fourth pair (K3 and N3) worth 2 points, you would add +2*(K3=N3) to the end of your arithmetic formula.

How do empty cells affect this formula?

If both cells in a pair (e.g., E3 and H3) are empty, the formula considers them a match because blank equals blank, awarding the points. Using the IF and COUNT method prevents the formula from scoring entirely empty rows.

Excel & Data Problems