Excel Formula to Dynamically Sum a Column on Another Sheet
Question details
Find a formula that uses the value in B4 to identify a matching header on Sheet2 and sum the corresponding column.

- 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.
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.
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.
On the sheet containing the lookup value in B4, click cell C4.
Type =SUM(OFFSET(Sheet2!$A$1,2,MATCH(B4,Sheet2!$A$1:$Z$1,0)-1,998,1)) in C4 and press Enter.
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.
Drag the fill handle from C4 down to calculate sums for the header values in B5, B6, and subsequent rows.

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. Open the workbook: Open your workbook in WPS Spreadsheets and check that Sheet2 contains headers in row 1.
- 2. Enter a header value: On the result sheet, enter the Sheet2 header you want to match in cell B4.
- 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. 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.

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.




