Use Cell References Correctly in an Excel SUMIFS Formula
Question details
Use cell references as SUMIFS criteria and copy the formula across rows and columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A summary table uses category labels in column B and year headings in row 1 to total matching values from another worksheet.
- Observed behavior
- The formula should use the category and year from the appropriate cells as it is copied across and down.
Check that Page1 contains years in column A, categories in column B, and values to sum in column C. Confirm that your summary table has category labels in column B and matching year headings in row 1.
Use mixed cell references in the SUMIFS criteria
Use this formula to total each category and year while copying the formula across and down.
In Excel, select cell C2 in the summary table, where the first category and year intersect.
Type =SUMIFS(Page1!$C:$C,Page1!$B:$B,$B2,Page1!$A:$A,C$1) in the formula bar and press Enter. Do not put quotation marks around $B2 or C$1.
Drag the fill handle from C2 across the year columns and down the category rows. The reference $B2 keeps column B fixed, while C$1 keeps row 1 fixed.

Build the same SUMIFS summary in WPS Spreadsheets
Use SUMIFS and mixed cell references in WPS Spreadsheets to total values by category and year, then fill the formula across and down.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Check the source data: Select the Page1 sheet and confirm that columns A, B, and C contain years, categories, and values respectively.
- 3. Enter the formula: On the summary sheet, select C2 and enter =SUMIFS(Page1!$C:$C,Page1!$B:$B,$B2,Page1!$A:$A,C$1).
- 4. Fill the summary table: Press Enter, then drag the fill handle across and down to calculate the other category and year totals.

Frequently Asked Questions
Why should I leave cell references out of quotation marks in SUMIFS?
Quotation marks make the contents text, so "$B2" is treated as a literal criterion rather than a reference to cell B2. Enter $B2 without quotation marks to use the cell's value.
What do $B2 and C$1 mean when I copy the formula?
$B2 fixes column B but allows the row to change when copied down. C$1 fixes row 1 but allows the column to change when copied across.
How do I use a comparison operator with a cell reference?
Put the operator in quotation marks and join it to the reference with an ampersand. For example, use ">="&C$1 as a SUMIFS criterion for values greater than or equal to the value in C1.
What should I check if SUMIFS returns an unexpected total?
Check that the source columns in the formula match your data and that the category and year cells match the source values. If your worksheet is not named Page1, replace Page1 in the formula with the correct sheet name.




