SUMIF in Google Sheets
SUMIF adds values when one condition is true. Use it to total an amount column by category or region, sum values above a threshold, or switch to SUMIFS when each row must satisfy multiple conditions.
Reviewed by the SheetsX team
The range is tested against one criterion, and matching rows contribute values from the optional sum range.
Adds four Software expenses from the Amount column and returns 3200 in the recorded demo.
SheetsX verified category, region, and threshold totals without changing the fictional expense data or labels.
How do you use SUMIF in Google Sheets?
Enter =SUMIF(range, criterion, sum_range) in an empty cell. For example, =SUMIF(A2:A9,"Software",D2:D9) checks each category in A2:A9 and adds the corresponding amount in D2:D9. Put text and comparison criteria inside quotation marks. If the cells being tested are also the values to add, you can omit sum_range.
Watch SheetsX write and verify four SUMIF formulas
The Agent totals expenses by category and region, then sums amounts above a numeric threshold. It replaces only the four incorrect summary values, updates their fill color, and confirms that the source data and labels remain unchanged.
The recording uses fictitious expense data. It shows the exact English prompt, the Agent's progress, the calculated values 3200, 2000, 3600, and 3650, and the final no-other-cells-changed verification.
“Fix the incorrect summary in G2:G5. Replace the hardcoded zeros with these SUMIF formulas and do not change any expense data or labels: G2 =SUMIF(A2:A9,"Software",D2:D9), G3 =SUMIF(A2:A9,"Services",D2:D9), G4 =SUMIF(C2:C9,"East",D2:D9), and G5 =SUMIF(D2:D9,">600",D2:D9). Remove the red fill from G2:G5, use a pale green fill, and verify the displayed results are 3200, 2000, 3600, and 3650. Reply in English and confirm that no other cells changed.”
How to use SUMIF in Google Sheets
Choose the cells that contain the condition, write one criterion, and align the optional sum range with the same rows before checking the result against visible data.
- 1
Choose the criterion range
Select the cells to test, such as A2:A9 for expense categories or C2:C9 for regions. Exclude the header row.
- 2
Write one criterion
Use exact text such as "Software" or a comparison such as ">600". Keep comparison operators and their values together inside quotation marks.
- 3
Align the sum range
When the values to add are in another column, provide a sum range with rows aligned to the criterion range, such as D2:D9.
- 4
Verify or switch to SUMIFS
Compare the result with a small visible sample. If every row must satisfy two or more conditions, use SUMIFS instead.
SUMIF formulas for common Google Sheets totals
These examples cover the category, region, and threshold patterns used in the demo. Replace the ranges and criteria with your own sheet structure.
| Use case | Formula or setting | What it does |
|---|---|---|
| Sum one category | =SUMIF(A2:A9,"Software",D2:D9) | Checks A2:A9 for Software and adds the corresponding values from D2:D9. |
| Sum another category | =SUMIF(A2:A9,"Services",D2:D9) | Returns 2000 from the three Services rows in the recorded expense table. |
| Sum one region | =SUMIF(C2:C9,"East",D2:D9) | Adds amounts whose row has East in the Region column and returns 3600 in the demo. |
| Sum values above a threshold | =SUMIF(D2:D9,">600",D2:D9) | Tests and sums the same Amount range, including only numeric values greater than 600. |
| Sum by a criterion stored in a cell | =SUMIF(A2:A100,F2,D2:D100) | Uses the value in F2 as the criterion so the summary can change without editing the formula. |
Ask SheetsX to write SUMIF and verify the result
A precise Agent prompt names the criterion range, criterion, sum range, destination cells, expected totals, and everything that must remain unchanged. That makes each formula and verification auditable.
Install SheetsX- Open a new English Agent chat for a clean task context.
- Name every criterion range, sum range, and destination cell explicitly.
- Provide the exact text, comparison, or cell-based criterion.
- Tell the Agent which source data, labels, and formatting must stay unchanged.
- Review both the formulas and their displayed totals before continuing.
SUMIF questions
What is the SUMIF syntax in Google Sheets?+
The syntax is =SUMIF(range, criterion, [sum_range]). Range contains the cells to test, criterion is the single condition, and the optional sum range contains the values to add for matching rows.
How do I use SUMIF with text?+
Put the text criterion inside quotation marks, for example =SUMIF(A2:A100,"Software",D2:D100). You can also reference a cell containing the criterion.
How do I sum values greater than a number?+
Combine the comparison operator and number inside one quoted criterion, such as =SUMIF(D2:D100,">600",D2:D100).
What is the difference between SUMIF and SUMIFS?+
SUMIF evaluates one criterion. SUMIFS is designed for multiple criteria and places the sum range first, followed by criterion-range and criterion pairs.
Why does my SUMIF total look wrong?+
Check that the criterion and sum ranges cover aligned rows, numbers are stored as numbers, text has no unexpected spaces, and comparison operators are quoted. Test the formula against a small visible sample.
Can SheetsX create SUMIF formulas without changing my source data?+
Yes. Name the destination cells and explicitly state which source ranges, labels, and formatting must remain unchanged. The demo verifies all four totals and reports that only G2:G5 changed.