Formula Errors

Excel Formula to Dynamically Sum a Column on Another Sheet

Steve KSteve K Nov 20, 2026 868 views

Question details

Find a formula that uses the value in B4 to identify a matching header on Sheet2 and sum the corresponding column.

How to Dynamically Sum a Column on Another Sheet in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs to sum a column on Sheet2 based on a header value that can change or be copied down from B4, without VBA or a hard-coded column reference.
Observed behavior
The desired formula finds the matching header on Sheet2 and sums the data beneath it.
Before you start

Check that Sheet2 has headers in row 1 and that B4 contains the header you want to match. The example below searches columns A through Z and sums rows 3 through 1000; adjust those limits for your data.

Solution 1Recommended

Sum the matching Sheet2 column with MATCH and OFFSET

Use this formula when B4 contains the header name and the data to sum starts in row 3 of Sheet2.

MATCH finds the position of the header in Sheet2's first row. OFFSET uses that position to select the corresponding data column, and SUM adds its numeric values.

1
Select the result cell

On the sheet containing the lookup value in B4, click cell C4.

2
Enter the formula

Type =SUM(OFFSET(Sheet2!$A$1,2,MATCH(B4,Sheet2!$A$1:$Z$1,0)-1,998,1)) in C4 and press Enter.

3
Adjust the range if needed

In the formula bar, change $A$1:$Z$1 to cover your headers. Change the OFFSET row offset and height to match your data; 2 starts at row 3, and 998 includes rows 3 through 1000.

4
Copy the formula down

Drag the fill handle from C4 down to calculate sums for the header values in B5, B6, and subsequent rows.

Sum the matching Sheet2 column with MATCH and OFFSET
Header matching: MATCH returns the first exact match. If no header matches B4, the formula returns #N/A; check the header text and the searched range.
WPS Spreadsheets

Sum a Matching Column Across Sheets in WPS Spreadsheets

Use MATCH, OFFSET, and SUM in WPS Spreadsheets to find a header on Sheet2 and sum its data column dynamically.

  1. 1. Open the workbook: Open your workbook in WPS Spreadsheets and check that Sheet2 contains headers in row 1.
  2. 2. Enter a header value: On the result sheet, enter the Sheet2 header you want to match in cell B4.
  3. 3. Add the sum formula: Select C4, enter =SUM(OFFSET(Sheet2!$A$1,2,MATCH(B4,Sheet2!$A$1:$Z$1,0)-1,998,1)), and press Enter.
  4. 4. Adapt and copy the formula: Edit the header range and OFFSET dimensions in the formula bar to fit your data, then drag C4's fill handle down for additional values in column B.
Sum different columns by changing the header value in B4Copy the formula down for multiple header valuesOpen and edit Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return #N/A?

MATCH returns #N/A when it cannot find the value in B4 in Sheet2!$A$1:$Z$1. Check for spelling differences or extra spaces, and expand the header range if the header is outside column Z.

How do I include more than 1,000 rows?

Edit the OFFSET height in the formula bar. For example, replace 998 with 1998 to sum rows 3 through 2000, provided those rows fit within the worksheet.

What happens if Sheet2 has duplicate headers?

MATCH selects the first exact match in the specified header range, so the formula sums only that column. Use unique headers if each column needs to be selected separately.

Can I use a different sheet name?

Yes. Replace both Sheet2 references in the formula with the actual sheet name. If the name contains spaces, enclose it in single quotes, such as 'Sales Data'!$A$1.