How to SUMIF across multiple columns in Excel

Why SUMIF only adds the first column

The sheet has sales by region for January to June, with North appearing on three rows. The obvious formula gives the sum range as the whole block B2:G9, and Excel accepts it without complaint.

But SUMIF sizes the sum range to match the criteria range. A2:A9 is one column wide, so Excel uses only B2:B9, the January column, and ignores February to June. The result looks plausible, which is why this goes unnoticed: 8,900 is a real number, it is just North's January sales. SUMIFS is no help either, as all of its ranges must be the same size.

Method 1: SUMPRODUCT across the whole block

SUMPRODUCT can work on a block. (A2:A9="North") makes a column of TRUE and FALSE, and multiplying it by B2:G9 turns every non-North row into zeros while keeping the North rows. SUMPRODUCT then adds everything that is left: all six months for the three North rows.

Use the multiplication form shown here. The comma form =SUMPRODUCT(--(A2:A9="North"),B2:G9) returns #VALUE!, because with commas every array must be the same shape, and a one-column test does not match a six-column block.

It works in every version of Excel and needs no Ctrl+Shift+Enter. The ranges must have the same number of rows: A2:A9 with B2:G10 is an error.

Method 2: a Total column, then a normal SUMIF

If you control the layout, add a Total column at the end of each row with =SUM(B2:G2) and fill it down (column H in the sample). Now there is one column to add, and an ordinary SUMIF does the job.

This is the easiest version to read and audit, and it handles text in the data well. SUM inside each row skips a stray "n/a" or a dash, while SUMPRODUCT multiplies the text and returns #VALUE! for the whole formula. With "n/a" typed in North's March cell, the SUMPRODUCT above breaks, and =SUM(FILTER(B2:G9,A2:A9="North")) still works in Excel 365 and 2021, returning 55,750 because SUM skips the text.

In Excel 365 you can also list every region with its total in one go with =LET(r,UNIQUE(A2:A9),HSTACK(r,SUMIF(A2:A9,r,H2:H9))): North 60,400, South 37,300, East 56,200, West 10,700.

Method 3: pick the month by its header

Often you want one month, chosen in a cell: J1 holds the region and J2 the month. INDEX with 0 as the row number returns the whole column whose position XMATCH finds in the header row, and because INDEX returns a real range, SUMIFS accepts it. Change J2 to Jun and the formula moves to column G. XMATCH needs Excel 365 or 2021; in older versions use MATCH(J2,B1:G1,0).

A tempting alternative is CHOOSECOLS, but SUMIFS only takes ranges and CHOOSECOLS returns an array, so Excel will not accept SUMIFS(CHOOSECOLS(...),...). Use CHOOSECOLS inside SUMPRODUCT instead: =SUMPRODUCT((A2:A9=J1)*CHOOSECOLS(B2:G9,XMATCH(J2,B1:G1))) gives the same 9,800.

For a two-way condition without INDEX, compare the header row as well: =SUMPRODUCT((A2:A9=J1)*(B1:G1=J2)*B2:G9). The column test runs across and the row test runs down, so only the cells where both are true are added.

Year to date: the first N months

For a running total up to a month, add a test on the column position. J3 holds 3 for "January to March". The months must be in order from left to right for this to mean year to date.

Writing it out by hand works too, as =SUMIFS(B2:B9,A2:A9,J1)+SUMIFS(C2:C9,A2:A9,J1)+SUMIFS(D2:D9,A2:A9,J1), but every new month means editing the formula. The position test updates when you change J3.

Which method to pick

Use a Total column and SUMIF when you can change the sheet: it is fast, readable and tolerant of text. Use SUMPRODUCT when the layout is fixed, such as a report someone else sends you, and the block holds only numbers and blanks. Use INDEX/XMATCH inside SUMIFS when the month comes from a drop-down, and the position test for year to date.

If the data has many rows and you need it every month, consider reshaping it into a long table (Region, Month, Sales). Then every question is a plain SUMIFS or a PivotTable, and a new month is new rows rather than a new column.