Google Sheets 2D SUMIF: Sum Values by Row and Column

Google Sheets has no built-in 2D SUMIF function, but you can sum a table by matching a row label and a column header with SUMIF, INDEX, and MATCH. For example, this approach can total every North entry in the February column, even when North appears on multiple rows.

What “2D SUMIF” means in Google Sheets

A regular SUMIF checks one range for a condition and adds values from a corresponding range. A two-dimensional sum adds another condition: it selects the relevant column by its header, then sums the rows that match a label. The conditions might be a department and a month, a product and a quarter, or a person and a project.

“2D SUMIF” is a description of the task, not the name of a Google Sheets function. The distinction matters because the function needs to know which of your table’s columns to add. A formula that checks only the row labels cannot also infer the intended column unless you tell it how to identify that column.

The method below is useful when the table has one label column, headers across the top, and numeric values in the body. It can add multiple matching rows. It assumes the column header you select occurs only once; a separate option later covers repeated headers.

Set up a small example table

Suppose a sheet has Region in A1, month names in B1:D1, and sales figures in B2:D4. The rows are North with 120 in January, 90 in February, and 110 in March; South with 80, 130, and 100; and North again with 50, 70, and 60. Put the region to find in G2 and the month to find in H2.

For North and February, the answer is 160: the two North rows contain 90 and 70 in that column. This example is deliberately set up with a repeated row label, because summing all matching rows is a common reason people need this technique. The result is not just the value from the first North row.

Keep the headers, criteria, and data in clearly separated areas when possible. In a real sheet, it is also helpful to use consistent labels: an extra space at the end of a header or a different spelling in the criteria cell can make an otherwise sound formula fail to match.

Use SUMIF with INDEX and MATCH

With region labels in A2:A4, month headers in B1:D1, values in B2:D4, and the requested region and month in G2 and H2, enter this formula:

=SUMIF($A$2:$A$4,G2,INDEX($B$2:$D$4,0,MATCH(H2,$B$1:$D$1,0)))

MATCH finds the position of the requested month in the header row. For February, it returns 2 because February is the second item in B1:D1. INDEX uses that position to supply the corresponding data column to SUMIF. The zero row argument tells INDEX to return the whole selected column, rather than just one cell. SUMIF then adds the values in that column where the region in A2:A4 equals G2.

In this example, the formula returns 160. The dollar signs keep the table ranges fixed if you copy the formula, while G2 and H2 remain relative references so the criteria can move with the formula. If you want to fill the formula down or across, adjust those references to suit the layout of your output area.

Build and adapt the formula step by step

For a different table, map each part of the formula to the matching part of your sheet. The layout for the example above is A2:A4 for the row labels, B1:D1 for the headers, B2:D4 for the numeric body, G2 for the row criterion, and H2 for the column criterion. Then adapt these parts:

Replace A2:A4 with the range containing the labels SUMIF should test.
Replace B1:D1 with the header row that contains the column names to match.
Replace B2:D4 with the value area under those headers. It must have the same number of columns as the header range and the same number of rows as the label range.
Replace G2 and H2 with the cells containing your chosen row and column criteria.
Use dollar signs on ranges you want to stay fixed when copying the formula.

The formula’s MATCH argument uses 0 for an exact match. That is generally what you want for category names, months, and other defined headers. The header text in H2 must match the text in the header row. If the headers are dates rather than month names, the underlying values and formats should also be consistent; two cells that look alike can hold different values.

When headers repeat: sum every matching column

The INDEX-and-MATCH version selects the first matching header. If a header such as “Actual” appears in more than one column and you want to add all columns with that header, use a matrix calculation instead. This option assumes numeric values in B2:D4, row labels in A2:A4, headers in B1:D1, and criteria in G2 and H2:

=SUMPRODUCT(($A$2:$A$4=G2)*MMULT($B$2:$D$4,TRANSPOSE(--($B$1:$D$1=H2))))

The header test creates a selection for every matching column. MMULT combines the selected columns into a total for each row, and SUMPRODUCT adds those row totals only where the label matches G2. If there are two columns headed February, both are included. With the original example, it also returns 160. This formula is less approachable than the first one and expects the data body to contain numbers, so use it only when duplicate headers are intentional and should all count.

Common errors and useful checks

If the formula returns #N/A, MATCH probably could not find the requested header. Check spelling, spaces, and whether the criteria cell contains the same kind of value as the header. If the result is zero rather than an error, check both the header match and the row labels; SUMIF returns zero when it finds no matching rows or when the matching sum cells are blank or zero.

Other checks that often resolve confusing results include:

Confirm that the header range and data range have the same number of columns.
Confirm that the label range and data range have the same number of rows.
Check for leading or trailing spaces in labels. TRIM can clean text in a helper column if the source data is inconsistent.
Make sure values intended for addition are numbers, not text that only looks numeric.
Check that the chosen header is unique if you are using the INDEX-and-MATCH version.

SUMIF criteria can use wildcard characters for text matching: an asterisk represents any sequence of characters, and a question mark represents one character. That can be useful for deliberately broad criteria, but it can also produce unexpected matches if a label contains those characters. Use exact labels and a clear criterion cell when you need a precise category total.

Choosing between this method and alternatives

For one row condition and one header condition, SUMIF with INDEX and MATCH is usually the most readable option. It makes the job of each function visible: MATCH locates a header, INDEX returns its data column, and SUMIF adds values for matching labels. It also handles repeated row labels naturally.

If you need more than one condition within each row—for example, region and salesperson—SUMIFS may be a better fit after selecting the required data column. If the goal is simply to look up one cell at the intersection of a row and column, rather than total multiple rows, a two-way lookup is more appropriate. A pivot table is useful when you want a report grouped by several categories and expect to explore or summarize the data repeatedly.

Google Sheets also has SUMPRODUCT, which is flexible for array-based calculations, but it is not automatically a simpler replacement. The matrix formula above is useful for repeated headers; for ordinary unique headers, the SUMIF approach is easier to inspect and maintain. Choose based on how the table is structured and what the result should include, rather than using a more complex formula by default.

Google Sheets and Excel differences

The formulas shown use functions available in Google Sheets and familiar to Excel users: SUMIF, INDEX, MATCH, SUMPRODUCT, MMULT, and TRANSPOSE. In Google Sheets, enter the formula in a cell in the web browser and press Enter. The result updates when the source values or criteria change.

Excel also supports the main SUMIF, INDEX, and MATCH approach, though details of array evaluation can differ for more complex formulas. The repeated-header MMULT option should be checked in the particular Excel version and workbook setup if you plan to move it between applications. Also, spreadsheet locales may require semicolons instead of commas between function arguments; that is a regional setting, not a different calculation. These examples use commas, as requested in the standard formula syntax.

Frequently Asked Questions

Does Google Sheets have a 2D SUMIF function?
No. There is no function named 2D SUMIF. Combine SUMIF with INDEX and MATCH to select a column by its header and sum rows matching a label.

Can I use two criteria with SUMIF?
SUMIF takes one criterion range and one criterion. For multiple row-level criteria, use SUMIFS, or use a formula designed to combine the row and column conditions. The formula in this guide uses the header to select a sum range and SUMIF for the row condition.

What happens if the row label appears more than once?
The SUMIF version adds every matching row in the selected column. In the example, it includes both North rows, which is why the February result is 160 rather than 90.

What if the column header appears more than once?
MATCH returns the position of the first matching header, so the basic formula uses only that column. Use the MMULT option if you want to sum every column whose header matches the criterion.

Can I use this with dates across the top?
Yes, if the header cells and the criterion cell contain comparable date values. Apply a date format to make them readable, but remember that formatting changes how a value appears, not the value used for matching.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.