Function Problems

Use Cell References Correctly in an Excel SUMIFS Formula

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 868 views

Question details

Use cell references as SUMIFS criteria and copy the formula across rows and columns.

Use Cell References Correctly in an Excel SUMIFS Formula
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.
Before you start

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.

Solution 1Recommended

Use mixed cell references in the SUMIFS criteria

Use this formula to total each category and year while copying the formula across and down.

1
Select the first result cell

In Excel, select cell C2 in the summary table, where the first category and year intersect.

2
Enter the SUMIFS formula

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.

3
Copy the formula

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.

Use mixed cell references in the SUMIFS criteria
Use quotes for operators, not cell references: A reference such as C$1 should not be quoted. If you need a comparison operator, concatenate it with the reference, for example, ">="&C$1.
Work with formulas in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets and use Open to select your Excel workbook.
  2. 2. Check the source data: Select the Page1 sheet and confirm that columns A, B, and C contain years, categories, and values respectively.
  3. 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. 4. Fill the summary table: Press Enter, then drag the fill handle across and down to calculate the other category and year totals.
Use SUMIFS with mixed references for reusable summary formulasOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.