Press Enter to search  ·  Esc to close

Claude AI — Excel Course
Use Claude to Build a SUMIFS Formula for Multi-Criteria Excel Reports
Progress1 of 4
Section 01

SUMIFS returning 0 — or you need to write one from scratch?

SUMIFS is one of those formulas that looks simple on paper… until you actually need to use it in a real report. The syntax is straightforward — you give it a sum range and then pairs of criteria range + criteria — but the moment you add three or four conditions, dates, wildcards, or references to another sheet, things get messy fast.

Most of the time the problem isn't deep logic. It's the small annoying stuff: the ranges aren't the same size, a date is formatted as text, you forgot the quotes around a text criterion, or one column is off by one. Excel just returns 0 and gives you zero clues.

SUMIFS formula — annotated
=SUMIFS($E$2:$E$500, $A$2:$A$500, "North", $B$2:$B$500, "Widget Pro", $C$2:$C$500, ">="&DATE(2026,1,1))
Sum rangeColumn E — values to add up
Criteria pair 1Column A must equal "North"
Criteria pair 2Column B must equal "Widget Pro"
Criteria pair 3Column C date on or after 1 Jan 2026
$ signsAbsolute refs — safe to copy

This is where Claude really shines. You describe in plain English what you want to sum and under which conditions, paste a few rows of sample data, and it writes the formula for you. If you already have a SUMIFS that's returning 0 or wrong numbers, you paste it in and Claude spots the mistake immediately.

Why SUMIFS returns 0 — the four most common culprits:

Returns 0 — common causes
Range size mismatch
SUM: E2:E100 · Criteria: A2:A99
Most common
Missing quotes on text
,"North", → should be ,"North", ✓
Easy fix
Date stored as text
Cell shows 01/01/2026 but type is TEXT
Invisible
Extra spaces in data
"North " ≠ "North" — TRIM() needed
Invisible
  • You explain the report in normal English and Claude turns it into a correct SUMIFS
  • You paste a broken formula and Claude tells you exactly why it's failing
  • Claude handles multiple criteria, dates, wildcards, and cross-sheet references
  • Claude explains every part of the formula so you can modify it yourself next time

SUMIFS vs SUMIF: SUMIF only handles one condition. SUMIFS handles multiple — and you should use SUMIFS even for single conditions, since the argument order is more consistent and easier to extend later.

Six steps — from describing what you need to a working, copy-safe formula.

OR logic — how to sum where Region is North OR South
The formula
=SUMIFS($E$2:$E$500, $A$2:$A$500, "North") + SUMIFS($E$2:$E$500, $A$2:$A$500, "South")
Two SUMIFS added together — one for each OR condition. Claude writes this automatically when you ask for OR logic.
01

Start with plain English

Before writing anything, write one clear sentence: "I want to sum the Revenue column where Region is North, Product is Widget Pro, and the Date is between 1 Jan and 31 Mar." This sentence becomes your prompt.

02

Show Claude your data structure

Tell Claude which column to sum and which columns contain the criteria. Pasting 3–4 sample rows makes a huge difference — Claude can see the real format of your dates, text, and numbers.

03

Get the formula

Claude will give you the full SUMIFS with all criteria pairs. If you need OR logic — for example North OR South — it will suggest writing two SUMIFS added together with +.

04

Test it first

Always test the formula on one row where you already know the correct answer before rolling it out across the whole report. If it returns the right number, you're good to go.

05

Debug if it returns 0

Paste the broken formula and 3 sample rows into Claude. The most common culprits: mismatched range sizes, missing quotes around text criteria, dates stored as text, or extra spaces. Claude will find it.

06

Lock the references

Once it works, make sure the ranges use absolute references — $A$2:$A$500 — so you can copy the formula across rows or columns without breaking it. Just ask Claude to add the $ signs if they're missing.

Tip: If you're not sure how many rows your data has, use a full column reference like $A:$A instead of $A$2:$A$500. It's slightly slower on huge datasets but eliminates the range-size mismatch problem entirely.

Copy these prompts into Claude. Replace the bracketed placeholders with your actual data and column structure.

Build from scratch prompt

Prompt — paste into Claude
I need a SUMIFS formula. Here's what I want: [describe in plain English, e.g. "Sum Revenue where Region is 'North', Product is 'Widget Pro', and Date is between 1 Jan 2026 and 31 Mar 2026"] My data is on sheet [name]. Column structure: - Sum range: column E (Revenue) - Criteria 1: column A (Region) - Criteria 2: column B (Product) - Criteria 3: column C (Date) Data runs from row 2 to row [last row]. Please write the SUMIFS with absolute references so I can copy it safely.

Debug prompt — returns 0

Prompt — paste into Claude
My SUMIFS is returning 0 even though there should be matches. Here's the formula: [paste formula] Here are 3 sample rows from my data: [paste rows] The expected result for these rows is [expected value]. Please tell me what's wrong and give me the corrected formula.

OR logic prompt

Prompt — paste into Claude
I need to sum where Region is either 'North' OR 'South'. How should I structure this with SUMIFS? Sum range is column E, Region is column A, data from row 2 to 500.
Checklist
Requirement described in plain English before writing any formula
Column structure and sample rows shared with Claude
Formula tested on at least one row with a known expected result
Criteria ranges confirmed as the same size as the sum range
Text criteria confirmed in quotes, dates confirmed as real dates not text
Absolute references added before copying formula across the report
OR conditions handled with two SUMIFS added together with +
Date criteria: if your SUMIFS uses date conditions, make sure the date column contains real Excel dates — not text that looks like a date. Check by selecting a date cell and looking at the format in the ribbon. If it says "Text", the dates need converting before SUMIFS will match them.