Define the question first
In this example A contains the department, B the order status and C the numeric amount. Row 1 is a header. We want Sales AND Complete, so both conditions must hold for the same row. Put the result in E2, outside the source table. The expected total is 320: row 2 contributes 120 and row 5 contributes 200.
Read the formula from the inside
C2:C5 is the range to add. A2:A5 is tested against the literal text Sales; B2:B5 is tested against Complete. The text criteria need double quotes. A row that meets only one condition contributes nothing. All three ranges start at row 2 and finish at row 5.
Describe the full range to AI
Try: “Excel 2016, A1:C5 has Department, Status, Amount. Add Amount where Department is Sales and Status is Complete; put the formula in E2.” If your real file ends at row 500, provide that full range rather than assuming the five-row example represents the entire file.
Worked example
A1:C5
| Department | Status | Amount |
|---|---|---|
| Sales | Complete | 120 |
| Support | Complete | 80 |
| Sales | Pending | 50 |
| Sales | Complete | 200 |
=SUMIFS(C2:C5,A2:A5,"Sales",B2:B5,"Complete")Teaching example, not a live AI response or an Excel execution record.
Try it step by step
- Enter the example into A1:C5 with C2:C5 stored as numbers.
- Copy the formula into E2 on the same worksheet.
- Check that the result is 320, then change one Pending status to Complete and confirm the total changes as intended.
- If you copy the formula elsewhere, decide whether the source references should be absolute, such as $C$2:$C$5.
Before you rely on the result
- Amounts stored as text can be ignored. Check a cell with ISNUMBER before trusting the total.
- Hidden or filtered rows still belong to the ranges. This formula is not a visible-rows-only total.
- Sales OR Support is a different condition from Sales AND Complete. Spell out the intended logic.