SUMIFS in Excel: product, region and reporting month

You need sales for one product, in one region, during one month. You could filter the spreadsheet, copy the visible rows and hope you remember every setting next time. I prefer to put those conditions into a formula. That gives the calculation a clear definition and makes changing the reporting period much less exciting.

The function for this job is SUMIFS. I will use six synthetic transactions to build a small report, explain every inclusion and exclusion, and show where a perfectly valid formula can answer the wrong question. The arithmetic is deliberately small enough to follow by reading the rows.

What SUMIFS combines

SUMIFS adds amounts from rows meeting all the supplied conditions. The product, region and date must describe the same transaction. Finding the product somewhere in the column and the region somewhere else does not qualify a row.

The Microsoft function reference puts the sum range first, followed by pairs consisting of a criteria range and a criterion. You can start with one condition and add the others:

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

I use English function names and commas in this version. The Polish function is SUMA.WARUNKÓW. Argument separators depend on regional settings, so your installation might expect semicolons. Check the formula hint rather than treating a copied separator as universal.

The result is a total, not a list of qualifying transactions. If someone asks which orders were included, the number alone will not answer. If the question concerns the number of transactions, a counting function is appropriate instead. Sales value and transaction count can look equally plausible in a report even though they describe different things.

Six transactions we can read in full

Put the headers in A1:E1 and the data in rows 2:7. All transactions and amounts below are synthetic. Column C contains actual Excel dates, and column E contains numbers. Currency symbols should come from formatting rather than being typed into the values.

Before calculating, I define the grain: one row represents one transaction with a status. An order-line export could contain several rows for the same order. Adding line amounts may then be correct, while adding an order total repeated on every line would inflate sales. SUMIFS cannot infer which model your export uses.

You can create the first date with =DATE(2026,10,1). This avoids relying on whether a short date such as 01/10 is interpreted as January or October. Microsoft documents the year, month and day arguments in the DATE reference.

Start with the product, then add the region

First I sum all coffee:

=SUMIFS($E$2:$E$7,$A$2:$A$7,"Coffee")

Rows 2, 3, 5, 6 and 7 qualify, producing 370. That includes November and a cancelled transaction. There is no reason to expect an October-only number yet: the formula has no date condition.

Now I add the region:

=SUMIFS($E$2:$E$7,$A$2:$A$7,"Coffee",$B$2:$B$7,"North")

Rows 2, 3, 6 and 7 remain, producing 250. The South transaction drops out. I like building a calculation this way because I can identify the rows removed by each new condition. With a larger source, I first work through a small sample that I can inspect completely.

A useful review question at this stage is whether the exclusions match the request. If the business wanted all regions, adding North would make the result worse even though the formula would still be valid. Correct syntax is only one part of a useful answer.

Express a month with two boundaries

Suppose the final request is completed coffee sales in North during October. Enter Coffee in H2, North in H3, =DATE(2026,10,1) in H4, =DATE(2026,11,1) in H5 and Completed in H6.

=SUMIFS($E$2:$E$7,$A$2:$A$7,$H$2,$B$2:$B$7,$H$3,$C$2:$C$7,">="&$H$4,$C$2:$C$7,"<"&$H$5,$D$2:$D$7,$H$6)

Both date conditions use column C. A date must be on or after the beginning of October and before the beginning of November. The ampersand joins a comparison operator to a cell value. Writing ">=H4" would put the letters H4 inside the criterion instead of obtaining the date from that cell.

Rows 2 and 3 meet every condition, so the result is 150. Row 4 fails the product test, row 5 the region test, row 6 the period test and row 7 the status test. I want this explanation next to the formula while learning. A number without an error message gives much less assurance.

An exclusive upper boundary also works well when the source includes times. A transaction at 18:00 on October 31 is earlier than midnight on November 1. A boundary of “less than or equal to October 31” using midnight could exclude later times on the final day. For imported timestamps, I also establish which time zone the source uses.

Filtering just a month number is weaker. October 2025 and October 2026 share the same number, so a report spanning several years could mix them. Explicit date boundaries identify one reporting interval and make that choice visible to whoever maintains the workbook later.

Keep every range aligned

Every range above starts at row 2 and ends at row 7. Corresponding positions need to describe the same transaction. The first product criterion must control the first amount, rather than an amount from the adjacent row.

Different range sizes are a documented cause of errors in Microsoft’s SUMIF/SUMIFS troubleshooting guide. Equal-sized ranges shifted by a row are also dangerous: a formula may look acceptable while pairing a product with the wrong amount.

I check the beginning, end and meaning of each range before expanding selections. In somebody else’s workbook I also look for subtotal rows mixed into the transaction data. Adding detail amounts together with their subtotals would count some values twice. No criterion can compensate for an unexplained mixture of levels.

The dollar signs keep references fixed when a formula is copied. Here the criteria cells are fixed too. A reporting grid may need mixed references instead, but I make that a deliberate choice. The behavior is explained in Microsoft’s reference guide.

When reviewing a copied report, inspect a cell several rows below the original as well as the first result. A correct first formula does not prove that the ranges remain correct after filling. This is especially useful when somebody has copied a calculation from an older workbook with a different layout.

AND within a formula, OR across alternatives

Two criteria saying Product equals Coffee and Product equals Tea in the same SUMIFS require one cell to equal both labels. An ordinary product column cannot satisfy that. Adding another range/criterion pair does not turn the operation into an OR selection.

For two disjoint labels, I can add two totals:

=SUMIFS($E$2:$E$7,$A$2:$A$7,"Coffee")+SUMIFS($E$2:$E$7,$A$2:$A$7,"Tea")

The result is 450. Each row has one product, so the Coffee and Tea groups do not overlap. The same approach becomes unsafe for “Coffee or North”: some transactions meet both conditions and would be added twice.

For overlapping rules I use a helper column that assigns one inclusion decision per row, or choose a different aggregation method. I first ask whether the groups are mutually exclusive. A plus sign combines totals; it does not deduplicate the transactions behind them.

This matters even when each individual total is correct. Two accurate numbers can produce an inaccurate combined report if their populations overlap. I write the intended population in words before deciding how to combine the calculations.

Diagnose zero before celebrating it

A zero can mean no qualifying sales or no matching records. It can also result from matching transactions whose amounts cancel each other out. Showing a matching transaction count beside the amount helps a reader distinguish these situations.

When I expect a nonzero total, I inspect product, region, date and status one at a time. Temporarily removing the last criterion can reveal where the matching population disappears. Changing every condition simultaneously makes it harder to explain what was wrong.

Numbers imported as text need separate attention. Applying a currency format is not a reliable conversion procedure. Microsoft describes converting numbers stored as text. I check decimal separators and units before conversion: 100 dollars and 100 cents are both valid numbers with very different meanings.

Labels can contain unexpected spaces too. I prefer cleaning a helper column and retaining the original source. Replacing an exact label with a very broad condition merely to obtain a nonzero result can hide the data problem and select unrelated transactions.

Wildcards change the population

A criterion such as "Coff*" selects names beginning with that fragment. An asterisk matches a sequence of characters; a question mark matches one character. A tilde before the wildcard, as in ~*, searches for the literal symbol instead.

This is useful for product families, but an identifier may contain a real asterisk. I display the actual criterion beside the report total. Replacing Coffee with a prefix pattern can increase the total without adding a single transaction to the source.

The intended match should be understandable to another reader. If a report has a product-family filter, label it as a family. A narrow-looking heading next to a broad wildcard condition is an invitation to misinterpret the number.

Growing data and separate worksheets

The fixed range E2:E7 is useful for learning. In a recurring report, new transactions may arrive below its end. The formula remains valid while silently ignoring them. This makes range coverage part of the reporting process rather than a one-time setup detail.

After converting the source into an Excel table named Sales, with columns Amount, Product and Region, I can write:

=SUMIFS(Sales[Amount],Sales[Product],$H$2,Sales[Region],$H$3)

Structured references adjust with table data; Microsoft documents their rules. I still check that a new row actually belongs to the table. A similarly colored cell beside a table does not establish that relationship.

If each month is stored on a separate sheet, one ordinary SUMIFS does not automatically consolidate the layouts. For a few consistent sheets, separate totals may be added. With a regular stream of files, I would first create one source table, for example through Power Query, and then build the report against it.

Also distinguish the scope of the calculation from the scope of the source. A report may correctly select October from a file that contains only half of October. Checking the maximum date and confirming that the import completed are useful operational steps before sharing the result.

A small exercise before your own report

Using the six rows, calculate all Coffee in North, then restrict it to October, then exclude Cancelled transactions. The expected totals are 250, 180 and 150. The final change comes from exactly one amount of 30.

Next, move the date in row 6 to October 31. The final total should become 220. Before changing a formula, explain why that row now qualifies. This checks your understanding of the conditions rather than your ability to remember argument order.

For the next step, read about IF and AND and OR. Start your own report with a plain sentence describing the measure, qualifying rows and reporting period. Once that sentence is clear, the formula has a much easier job.

Make the handover readable

I keep the numerical result in a numeric cell and place its description beside it. A heading such as “Completed coffee sales, North, October” tells another reader much more than “Sales”. It also avoids forcing someone to reverse-engineer the report definition from a long formula.

Returns need an explicit decision too. A positive transaction of 100 and a return of -100 produce zero even though two events occurred. If the report measures a balance, that may be correct. If it measures sales before returns, give it that name and use a consistent definition. Do not remove negative amounts simply because the resulting total looks more pleasant.

With one condition, you may also encounter SUMIF. Its argument order differs:

=SUMIF($A$2:$A$7,"Coffee",$E$2:$E$7)

Here the sum range comes last. In SUMIFS it comes first. Renaming a function and appending criteria is therefore an unsafe conversion. I often start with SUMIFS when I expect a report to grow, as adding a region then means adding a new pair. That is an organizational preference rather than a performance claim.

For a complete matrix of every product and region, consider a PivotTable rather than maintaining many similar formulas. Whatever the tool, investigate differing report totals by comparing their populations and measure definitions first. A formula cannot decide whether your team intended the purchase date or the shipping date.