Excel SUMIFS returning 0 on Mac: causes and fixes for accountants in Microsoft 365

A SUMIFS that returns 0 is usually not doing the math wrong. It is reporting that nothing matched. Most of the time the numbers are there, but Excel sees them as text, or the criteria do not match the cells exactly. Once you know where to look, most of these take minutes to fix.

How SUMIFS decides which rows to add

SUMIFS adds the cells in a sum range when every condition you give it is true. The syntax is SUMIFS(sum_range, criteria_range1, criteria1, and so on). Microsoft puts the sum range first. The older SUMIF function puts it last, so keep the two formulas separate in your head.

Each criteria range must have the same number of rows and columns as the sum range. Microsoft allows up to 127 range and criteria pairs. Text criteria need quotation marks. Inside the sum range, TRUE counts as 1 and FALSE counts as 0.

Read the formula as a set of tests. Each row must pass every test to count. If no row passes all of them, the total is 0. That sounds obvious, but it explains most zeros. The formula is not broken. The tests are stricter than your data.

Six causes behind a zero result

A third-party guide lists six common causes, and they match the rules Microsoft gives for SUMIFS. Work through them in order, because the first three are the quickest to test.

1. Numbers or dates stored as text. The cell looks like a number, but the test does not see it as one.
2. Criteria typed inside quotes. A cell reference inside quotation marks is read as plain text.
3. Trailing or non-breaking spaces in criteria or source cells. Two identical-looking values can still differ.
4. Ranges that drift after you insert, delete, or sort rows. The sum range and criteria range stop covering the same data.
5. OR logic written as AND. SUMIFS needs every condition to pass on the same row.
6. Manual calculation or a swapped argument order. Both make a total look settled when it is not.

The first three causes sit in the data. The last three sit in the formula or the workbook settings. Start with the data, since those three are the ones you can test in a few minutes.

What a zero costs an accountant

A zero in a total does not look like an error. It looks like a real answer. Someone can read it, sign off on it, and move on. The cost is less about the formula and more about what happens next.

Time is the first cost. DOSS surveyed 1,003 US operations professionals in 2026. Its teams averaged 3.6 hours a week fixing spreadsheet mistakes. Those respondents are not accountants, so treat the figure as a rough signal.

DOSS also puts a price on one significant error at $4,315 on average. That is an operations figure too. Your own cost depends on how many clients rely on the total and how late you catch it.

A wider review of business spreadsheets reported that 94% of spreadsheets used in decisions contain errors. The Phys.org write-up covers a 2024 literature review and gives no sample size, so read it as context, not proof.

Accountants lose time in other ways too. A 2026 survey found accountants lose about 5 hours a week moving, re-entering, or reconciling information across tools. That survey did not measure spreadsheet zeros, so it is adjacent evidence.

Check the cells before you touch the formula

Diagnose first and edit later. Click a blank cell beside your data. Type =ISNUMBER(C2), using the first cell in your sum range in place of C2. TRUE means Excel sees a real number. FALSE means the cell holds text.

Then test the criteria. In a blank cell next to a criteria value, type =LEN(A2) with the criteria cell in place of A2. Compare the count with the length you expect. A longer count than expected means hidden characters.

Look at the cells as well. Text numbers often sit left-aligned, while real numbers sit right-aligned by default. Excel may also show a small green triangle in the corner of a text number. Those hints help, but the ISNUMBER test is the proof.

Convert numbers and dates stored as text

Microsoft's fix is Convert to Number. Select the affected cells. Click the error indicator, the small warning icon at the top left of the selection. Then choose Convert to Number. Microsoft's page gives a Windows keyboard shortcut and no Mac steps, so use the mouse.

If the warning icon never appears, background error checking may be off. On a Mac, open Excel's settings from the Excel menu, then find Error Checking under Formulas and Lists. Menu names change between versions, so search Excel's help if you cannot find them.

Dates follow the same rule. A date stored as text never falls inside a date range, even when it looks right. Convert the column, run the ISNUMBER test again, and then re-run the total. Do not trust a date that only looks like one.

Do not convert everything. Account codes, invoice numbers, and postcodes can carry leading zeros that matter. Converting them can strip those zeros, which breaks matches. Leave those columns as text and match them as text on both sides.

Fix criteria typed inside quotes

This is the most common cause with dates and numbers, according to the Master Excel Functions guide. Say your criteria cell holds a start date. A formula such as =SUMIFS(C:C, A:A, ">=K6") treats the characters >=K6 as the criteria. It never reads the value in K6.

Join the operator to the cell with an ampersand instead. Use =SUMIFS(C:C, A:A, ">="&K6). The quotes wrap only the operator, and the ampersand attaches the value in K6. Use the same pattern for other comparison tests.

Test the new formula on one row before you fill it down the column. If it still returns zero, check the cell K6 with ISNUMBER and make sure it holds a real date or number.

Remove hidden spaces and non-breaking spaces

Trailing spaces are invisible. Acme and Acme with one space at the end look the same in a cell, but SUMIFS sees two different values. Use a helper column to find out. In a blank column, type =TRIM(A2) and fill it down. Compare the results with the original.

TRIM does not remove every space. Text copied from a web page or a PDF often carries a non-breaking space. Use =TRIM(SUBSTITUTE(A2, UNICHAR(160), " ")) to swap that character for a normal space first. UNICHAR(160) returns the non-breaking space, so the formula targets exactly that character.

Clean both sides of the match. A clean criteria cell still returns zero if the source column is dirty. When the fixed column is right, paste the values back over the original, but only after you save a copy. Use Home > Paste > Values for that step.

Find & Replace can help too, through Home > Find & Select > Replace. Be careful. Replacing every space in a sheet can break names that need spaces. Run it on a copy, and only on the columns you checked.

Line up the sum range and criteria ranges

Every range in SUMIFS must match in shape. Microsoft says each criteria range needs the same number of rows and columns as the sum range. When the sizes differ, you usually see an error value instead of a total. That error is loud, so it is easy to spot.

The zero is the quiet version. It appears when the ranges are the right size but point at the wrong rows. That often happens after you insert or delete rows, or sort one column without the rest of its data. Sorting a single column is a classic way to break a match.

Whole-column references such as A:A stay aligned when you insert rows, because they always cover the full column. They also pick up new entries without edits. Use them for ledgers that grow, and check the total after each big paste.

Watch the argument order too. In SUMIFS the sum range comes first. If you copy a SUMIF formula and forget to reorder it, the test runs against the wrong column. Read every argument in the formula bar.

Handle OR conditions and multiple criteria

SUMIFS only does AND. Every condition must pass for a row to count. If you want rows where the region is East or West, one SUMIFS cannot do it. A row cannot be both, so the total returns zero.

Two fixes work. Add two SUMIFS formulas together, one for East and one for West. Or wrap an array constant in SUM, such as =SUM(SUMIFS(C:C, A:A, {"East","West"})). Compare both versions on the same data before you trust either one.

Several AND conditions work fine. Each extra criteria pair narrows the rows that count. You can add many pairs, up to the Microsoft limit of 127, so you will rarely hit that limit.

Check calculation mode and the formula itself

Sometimes the formula is right and the total is stale. If calculation is set to manual, Excel may not refresh the result after you change the data. Go to Formulas > Calculation Options and choose Automatic. If your version uses different menu names, search Excel's help for calculation options.

Read the full formula in the formula bar before you change anything. Look for a wrong column letter, a sheet name you did not expect, or a criteria cell on a different row. Compare it with a formula that works elsewhere in the same workbook.

Which Excel version you need on a Mac

Microsoft lists SUMIFS for Excel 2021, Excel 2024, and Microsoft 365, including the Mac versions. The latest Excel for Mac release in Microsoft's notes is 16.114.3, dated Oct 27, 2026. Its notes list only quality and performance changes, so do not expect an update to fix a formula mistake.

Microsoft says macOS 14 is the minimum for receiving updates to Microsoft 365 and Office apps. If your Mac runs an older system, the fixes here still apply to the version you have. You may just not get the newest builds.

Numbers on Mac: what changes

Numbers is Apple's spreadsheet app. Apple's help says its SUMIFS-type functions accept numeric comparison operators, wildcards, and regular expressions in conditions. That covers the operator and wildcard problems. It does not cover every text-number case.

The sources I checked do not show how Numbers treats a text number inside a SUMIFS condition. Apple's compatibility page lists SUMIFS as supported for Microsoft Office files, but it describes import, not identical behavior. Test a known row before you rely on a Numbers total. The steps for bringing Excel files over are in how to open an Excel file in Numbers.

Test a fix without wrecking the workbook

Before you change a client file, copy the sheet. Right-click the sheet tab, choose Move or Copy, and tick the option to create a copy. Run every fix on that copy. Compare the totals against a source you trust, such as a bank statement or a trial balance.

Keep one check row. Put a manual sum of a few rows you can verify by hand next to the SUMIFS total. If the two differ, something upstream is still wrong. The check takes a minute, and it stops a silent zero from getting through.

Save the fixed workbook under a new name and keep the old one as a record. Accountants often need to show what changed and why. A dated copy makes that simple.

Why these mistakes keep coming back

Most of these errors start at data entry, not in the formula. DOSS asked operations teams which error types they see. Manual data entry came first at 59%, then formula errors at 46% and copy-paste issues at 45%.

Those percentages add to more than 100%, so they are not one split of a single total. The mix is still a useful clue. Pasting from a PDF, a bank portal, or a web page brings in the formatting that SUMIFS trips on.

A simple habit helps. After you paste data in, check one cell with ISNUMBER and one with TRIM. Note where each column came from. A note takes seconds and can save a rebuild later.

Mistakes that bring the zero back

• Trusting a total without a check row. A zero can survive a fix that only touched one column.
• Fixing one side of the match. A clean criteria cell cannot rescue a dirty source column.
• Running Find & Replace across the whole sheet. Spaces inside names can disappear.
• Converting account codes that need leading zeros. The codes change, and the matches fail.
• Assuming a Mac menu matches the Windows steps you found online. Menu paths differ, so check the Mac version.

Where Lazur helps, and where it does not

Lazur is a voice assistant for the Mac. Hold ⌥ ⌘ to ask about anything on screen, and Lazur answers out loud. It can point at the cell you mean and explain what it sees. It does not click buttons, type into Excel, or edit your workbook.

For steps, ask Lazur to walk you through it. Lazur guides you one click at a time and waits for each click. You stay in control of the file. That helps when you know the goal but not where the menu lives.

• Explains a formula out loud while you hold ⌥ ⌘.
• Circles and labels the cell you ask about, so you can check it for spaces or quotes.
• Walks you through Convert to Number one click at a time.
• Drafts a short note to a client or colleague about a corrected figure, for you to review.
• Dictates clean text at the cursor with fn, in any app.

Plans and trial details are on the pricing page. Check it for the plan that fits your use. That page is the current source, not this article.

What to do next

Start with ISNUMBER on one cell. If it returns FALSE, convert the column. If the criteria cell has a stray space, trim it. Then check the total against a row you can verify by hand. Once the zero is gone, look at the wider close. Excel help for accountants on Mac covers month-end steps, and the XLOOKUP text and number fix covers a related mismatch.