SUMIF and SUMIFS in Excel: totals by customer and month from a ledger

The SUMIF function in Excel adds the values that meet one condition; SUMIFS adds the values that meet several. With them you can turn a list of invoices into totals by customer, month or region, and a customer-by-month grid comes from a single formula filled across and down. This guide works both on a ten-line sales ledger and ends with the check that the grid equals the ledger.

SUMIF and SUMIFS syntax

=SUMIF(range, criteria, [sum_range])

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The sum range comes last in SUMIF and first in SUMIFS. Microsoft's SUMIFS page flags the same difference. SUMIF tests range against criteria and adds the matching cells of sum_range; leave sum_range out and it adds the tested cells themselves. SUMIFS adds sum_range only where every criteria pair is true.

SUMIFS with one pair does exactly what SUMIF does. Standardize on SUMIFS and the argument order never changes when you add a second condition.

The ledger

Ten invoice lines for Q1 2026, in USD, with one credit memo. Select the data, press Ctrl+T, and on the Table Design tab name the table Ledger:

The ledger total is $56,260. A Table lets the formulas name columns, as in Ledger[Net amount], and those references grow when next month's rows are added.

Total for one customer

With SUMIF:

=SUMIF(Ledger[Customer],"Harlow Foods",Ledger[Net amount])

The SUMIFS equivalent:

=SUMIFS(Ledger[Net amount],Ledger[Customer],"Harlow Foods")

Both return 18,400: $12,400 + $7,200 − $1,200. The credit memo is a negative line, so it reduces the total without any extra step. In practice put the customer name in a cell, say A2, and write Ledger[Customer],A2, so one formula serves every customer.

Customer by month grid

On a sheet named Grid, list the customers in A2:A6 and type the first day of each month in B1:D1: 1/1/2026, 2/1/2026 and 3/1/2026, formatted as mmm so they read Jan, Feb, Mar. In B2:

=SUMIFS(Ledger[Net amount],Ledger[Customer],$A2,Ledger[Date],">="&B$1,Ledger[Date],"<"&EDATE(B$1,1))

The two date criteria keep dates on or after the first of the month and before the first of the next. EDATE returns the date a given number of months after a start date, so EDATE(B$1,1) is the next month's start. The mixed references let one formula fill the grid: $A2 keeps column A as the formula moves right, and B$1 keeps row 1 as it moves down.

Fill it by copying B2 and pasting into B2:D6. Dragging the fill handle to the right shifts single-column Table references such as Ledger[Net amount] to the next column over; copy and paste leaves them alone. Add row totals in column E with =SUM(B2:D2) and column totals in row 7.

A pivot table on the same ledger gives the same month totals by region. Building both is a quick cross-check, and the formula grid is the one that stays put in a report layout.

Criteria operators and wildcards

A criterion is text: the operator goes inside quotation marks, and a cell or date is joined on with &. Four examples on the ledger:

Not equal to Northeast:

=SUMIFS(Ledger[Net amount],Ledger[Region],"<>Northeast")

Returns 19,200: South $16,900 plus West $2,300.

Lines over 5,000:

=SUMIFS(Ledger[Net amount],Ledger[Net amount],">5000")

Returns 43,760, from five lines: $12,400, $8,760, $5,500, $7,200 and $9,900. The criteria range and the sum range can be the same column.

Customer names ending in "Inc.":

=SUMIFS(Ledger[Net amount],Ledger[Customer],"*Inc.")

Returns 18,660, Brightwell Inc.'s two lines. An asterisk matches any run of characters and a question mark matches one; put a tilde before either to match it literally, as in "~*".

The excluded region in a cell, with G1 holding Northeast:

=SUMIFS(Ledger[Net amount],Ledger[Region],"<>"&G1)

Returns 19,200 again, and changes when G1 does. Text criteria are not case-sensitive, so "northeast" matches too.

The check: the grid equals the ledger

Every line of the ledger belongs to exactly one customer and one month, so the grid must add back to the ledger:

=SUM(B2:D6)-SUM(Ledger[Net amount])

This returns 0. Check the other directions as well: the row totals in column E add to 56,260, and so do the column totals in row 7. If those two disagree, a total formula covers the wrong range.

A nonzero difference means some lines fall outside the grid. If A5 read "Kestrel Supply", that row would total zero and the check would show -9,300. A line dated April 2, 2026 in a Q1 grid shows as a difference of its own amount. Where the month boundaries fall is a period cut decision; the check makes sure none of it is lost silently.

Where it goes wrong

Swapped argument order. Rewriting a SUMIF as a SUMIFS without moving the sum range to the front adds the wrong column or returns an error.

Month-end written as "<="&end_date when dates carry times. A line at 2:30 PM on March 31 is later than March 31 at midnight, so it is dropped. Use "<"&EDATE(start,1), the next month's first day.

Ranges of different sizes. In SUMIFS every criteria range must have the same number of rows and columns as the sum range, or the formula returns #VALUE!. Whole Table columns avoid this, because they are always the same length.

Names spelled differently. "Harlow Foods " with a trailing space, or "Harlow Foods Inc", totals zero without any error. The grand-total check catches it.

Dates stored as text. An export that writes dates as text never meets a date criterion, and the grid fills with zeros. =ISNUMBER(Ledger[@Date]) returns FALSE for those rows; convert them before building the grid.

From totals to coverage

The zeros in the grid are information. Five of the fifteen cells are months in which a customer bought nothing: Northgate Retail in March, Brightwell Inc. in February, Kestrel Supplies in January and Ashby Engineering in January and March. Counting the customers who bought in each month is account coverage; a customer whose zeros run to the latest month is on the way to the dormant list. The row totals rank into A, B and C customers, and the same SUMIFS by month underlies a sales seasonality index.

Covirage builds the customer-by-month grid from the uploaded ledger, checks it against the ledger total, and lists the zero cells as accounts gone quiet; deterministic tools do the sums and the external AI model explains them. See every customer by month from your own ledger, with the gaps listed as accounts nobody has spoken to: account coverage.

title: "SUMIF and SUMIFS in Excel: totals by customer and month from a ledger" description: "The SUMIF function in Excel and its multi-condition version SUMIFS, worked on a ten-line sales ledger: the syntax and argument order, a total for one customer, a customer-by-month grid built from one formula, date-range and not-equal-to criteria, wildcards, and the check that the grid equals the ledger total." seoTitle: "SUMIF function in Excel: SUMIFS by customer and month" metaDescription: "SUMIF adds the cells that meet one condition; SUMIFS adds those meeting several. Syntax, date-range criteria and a customer-by-month grid that reconciles." date: 2026-09-30 category: guides solution: account-coverage keywords: ["sumif function in excel", "sumifs", "excel formulas sumifs examples", "sumif vs sumifs", "sumifs date range", "sumifs multiple criteria", "sumif not equal to"] answer: "SUMIF adds the values that meet one condition: =SUMIF(range, criteria, [sum_range]). SUMIFS adds values that meet several: =SUMIFS(sum_range, criteria_range1, criteria1, ...). Note the sum range comes first in SUMIFS and last in SUMIF. For a month, give two date criteria: ">="&start and "<"&EDATE(start,1). A customer-by-month grid built this way must sum to the ledger total." faq:

• q: "What is the difference between SUMIF and SUMIFS?" a: "SUMIF takes one condition and puts the sum range last. SUMIFS takes one or more conditions and puts the sum range first. SUMIFS does everything SUMIF does, so many teams use it everywhere to avoid mixing up the argument order."
• q: "How do I use SUMIFS with a date range?" a: "Add the date column twice with two criteria: ">="&start_date and "<"&end_date. For a calendar month use "<"&EDATE(start_date,1), which also handles times on the last day of the month."
• q: "Can SUMIFS use OR conditions?" a: "Not directly; all criteria in SUMIFS must be true together. For OR, add two SUMIFS together, or use =SUM(SUMIFS(sum_range,criteria_range,{"Northeast","South"})) with an array of values, which adds the two results in one formula."
• q: "Why does my SUMIFS return 0?" a: "Usually the criteria do not match exactly: text with trailing spaces, numbers stored as text, dates stored as text, or a criterion missing its quotation marks or & join. Test one criterion at a time with COUNTIFS."

The SUMIF function in Excel adds the values that meet one condition; SUMIFS adds the values that meet several. With them you can turn a list of invoices into totals by customer, month or region, and a customer-by-month grid comes from a single formula filled across and down. This guide works both on a ten-line sales ledger and ends with the check that the grid equals the ledger.

SUMIF and SUMIFS syntax

=SUMIF(range, criteria, [sum_range])

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The sum range comes last in SUMIF and first in SUMIFS. Microsoft's SUMIFS page flags the same difference. SUMIF tests range against criteria and adds the matching cells of sum_range; leave sum_range out and it adds the tested cells themselves. SUMIFS adds sum_range only where every criteria pair is true.

SUMIFS with one pair does exactly what SUMIF does. Standardize on SUMIFS and the argument order never changes when you add a second condition.

The ledger

Ten invoice lines for Q1 2026, in USD, with one credit memo. Select the data, press Ctrl+T, and on the Table Design tab name the table Ledger:

The ledger total is $56,260. A Table lets the formulas name columns, as in Ledger[Net amount], and those references grow when next month's rows are added.

Total for one customer

With SUMIF:

=SUMIF(Ledger[Customer],"Harlow Foods",Ledger[Net amount])

The SUMIFS equivalent:

=SUMIFS(Ledger[Net amount],Ledger[Customer],"Harlow Foods")

Both return 18,400: $12,400 + $7,200 − $1,200. The credit memo is a negative line, so it reduces the total without any extra step. In practice put the customer name in a cell, say A2, and write Ledger[Customer],A2, so one formula serves every customer.

Customer by month grid

On a sheet named Grid, list the customers in A2:A6 and type the first day of each month in B1:D1: 1/1/2026, 2/1/2026 and 3/1/2026, formatted as mmm so they read Jan, Feb, Mar. In B2:

=SUMIFS(Ledger[Net amount],Ledger[Customer],$A2,Ledger[Date],">="&B$1,Ledger[Date],"<"&EDATE(B$1,1))

The two date criteria keep dates on or after the first of the month and before the first of the next. EDATE returns the date a given number of months after a start date, so EDATE(B$1,1) is the next month's start. The mixed references let one formula fill the grid: $A2 keeps column A as the formula moves right, and B$1 keeps row 1 as it moves down.

Fill it by copying B2 and pasting into B2:D6. Dragging the fill handle to the right shifts single-column Table references such as Ledger[Net amount] to the next column over; copy and paste leaves them alone. Add row totals in column E with =SUM(B2:D2) and column totals in row 7.

A pivot table on the same ledger gives the same month totals by region. Building both is a quick cross-check, and the formula grid is the one that stays put in a report layout.

Criteria operators and wildcards

A criterion is text: the operator goes inside quotation marks, and a cell or date is joined on with &. Four examples on the ledger:

Not equal to Northeast:

=SUMIFS(Ledger[Net amount],Ledger[Region],"<>Northeast")

Returns 19,200: South $16,900 plus West $2,300.

Lines over 5,000:

=SUMIFS(Ledger[Net amount],Ledger[Net amount],">5000")

Returns 43,760, from five lines: $12,400, $8,760, $5,500, $7,200 and $9,900. The criteria range and the sum range can be the same column.

Customer names ending in "Inc.":

=SUMIFS(Ledger[Net amount],Ledger[Customer],"*Inc.")

Returns 18,660, Brightwell Inc.'s two lines. An asterisk matches any run of characters and a question mark matches one; put a tilde before either to match it literally, as in "~*".

The excluded region in a cell, with G1 holding Northeast:

=SUMIFS(Ledger[Net amount],Ledger[Region],"<>"&G1)

Returns 19,200 again, and changes when G1 does. Text criteria are not case-sensitive, so "northeast" matches too.

The check: the grid equals the ledger

Every line of the ledger belongs to exactly one customer and one month, so the grid must add back to the ledger:

=SUM(B2:D6)-SUM(Ledger[Net amount])

This returns 0. Check the other directions as well: the row totals in column E add to 56,260, and so do the column totals in row 7. If those two disagree, a total formula covers the wrong range.

A nonzero difference means some lines fall outside the grid. If A5 read "Kestrel Supply", that row would total zero and the check would show -9,300. A line dated April 2, 2026 in a Q1 grid shows as a difference of its own amount. Where the month boundaries fall is a period cut decision; the check makes sure none of it is lost silently.

Where it goes wrong

Swapped argument order. Rewriting a SUMIF as a SUMIFS without moving the sum range to the front adds the wrong column or returns an error.

Month-end written as "<="&end_date when dates carry times. A line at 2:30 PM on March 31 is later than March 31 at midnight, so it is dropped. Use "<"&EDATE(start,1), the next month's first day.

Ranges of different sizes. In SUMIFS every criteria range must have the same number of rows and columns as the sum range, or the formula returns #VALUE!. Whole Table columns avoid this, because they are always the same length.

Names spelled differently. "Harlow Foods " with a trailing space, or "Harlow Foods Inc", totals zero without any error. The grand-total check catches it.

Dates stored as text. An export that writes dates as text never meets a date criterion, and the grid fills with zeros. =ISNUMBER(Ledger[@Date]) returns FALSE for those rows; convert them before building the grid.

From totals to coverage

The zeros in the grid are information. Five of the fifteen cells are months in which a customer bought nothing: Northgate Retail in March, Brightwell Inc. in February, Kestrel Supplies in January and Ashby Engineering in January and March. Counting the customers who bought in each month is account coverage; a customer whose zeros run to the latest month is on the way to the dormant list. The row totals rank into A, B and C customers, and the same SUMIFS by month underlies a sales seasonality index.

Covirage builds the customer-by-month grid from the uploaded ledger, checks it against the ledger total, and lists the zero cells as accounts gone quiet; deterministic tools do the sums and the external AI model explains them. See every customer by month from your own ledger, with the gaps listed as accounts nobody has spoken to: account coverage. To return one value by key rather than total many, see XLOOKUP in Excel, and to put the grid on one page with charts, see how to build an Excel dashboard.