Use SUMIFS in Google Sheets to add amounts only when the matching row passes every condition. For example, =SUMIFS(D2:D100,C2:C100,"Approved",B2:B100,"Art") sums column D for rows with Approved in column C and Art in column B.
Put the sum range first, then add each criteria range and its criterion as a pair. Start and end the ranges on corresponding rows. If the total is zero or looks too high, inspect the matching records, their numeric values and any date boundaries before changing the formula.
- Put the amounts to add in the first argument.
- Add criteria as range and condition pairs.
- Use separate pairs when every condition must pass.
- Use a start boundary and the next day after an inclusive end date for timestamps.
- Check the matching rows as well as the total.
Build a small total and check it by hand
Put Date, Club, Status and Amount in A1:D1. Put Art, Approved and 12 in B2:D2. In rows three through five use Art, Pending and 8, Science, Approved and 15, then Art, Approved and 7. The dates are optional for this first check.
=SUMIFS(D2:D5,C2:C5,"Approved",B2:B5,"Art")Rows two and five pass both tests, so the answer is 19. The pending Art row is excluded because it fails the status test. The approved Science row is excluded because it fails the club test. Change the Pending row to Approved and the answer should rise to 27. That confirms that the criteria range and amount range refer to the same records.
Read the formula from the amount outward
D2:D5 contains the amounts. C2:C5 is the status range, and Approved is its criterion. B2:B5 is the club range, and Art is its criterion. Read each pair as a short question about a row. A row contributes only when both answers are yes.
Keep the header out of the calculation
If row one holds headings, start all ranges in row two. When the table grows, extend the amounts and both criteria ranges consistently. A formula that stops on row five will not include row six. Check a newly added record rather than relying on a total that previously worked.
Use criteria from cells instead of hard-coded labels
Put the club selector in G2 and the status selector in H2. Use their cell references in the criteria positions.
=SUMIFS($D$2:$D$100,$B$2:$B$100,G2,$C$2:$C$100,H2)Use the dollar signs when the source ranges should remain fixed as you copy the formula. If the formula is copied down a report, G2 and H2 can become G3 and H3 while the data stays in rows 2 to 100. Inspect the copied formula once, and verify one group against a manual total.
Give empty selectors a specific meaning
An empty selector can accidentally ask for rows with a blank value. Decide whether the report should show a prompt, match blank records or show an overall total. This example uses a prompt when either choice is missing.
=IF(OR(G2="",H2=""),"Choose a club and status",SUMIFS($D$2:$D$100,$B$2:$B$100,G2,$C$2:$C$100,H2))This prompt is text. If another calculation needs a number, consider keeping the message in a separate cell and giving the total a consistent numeric output. Test how the report treats both a missing selection and a genuine zero.
An All option needs explicit logic
If a dropdown offers All, a direct comparison searches for the literal label All. When All means all clubs, use a branch that omits the club condition and retains other required criteria. Check that the status filter still applies. Write down what All means before building the report.
Use operators with numeric criteria
An operator such as greater than belongs in the criterion. For a threshold stored in G2, join the operator to that cell value.
=SUMIFS(D2:D100,D2:D100,">="&G2,C2:C100,"Approved")This adds approved amounts of at least the threshold. It checks and sums the same amount column, which is appropriate when the amount itself determines eligibility. If the task sums hours based on a score, use the hours as the sum range and the score as the criteria range instead.
Check the boundary itself
With a threshold of 10, test values of 9, 10 and 11. The operator >= includes 10. The operator > excludes it. Confirm whether the instructions say “at least” or “more than.” A report can be wrong by one category or one record simply because the boundary is different.
Keep numeric amounts numeric
Imported amounts that look like numbers may be stored as text. Inspect the actual type or compare with the approved source. Clean an authorised working copy when necessary. Confirm decimal separators and units before converting. If 1.5 hours and 90 minutes appear in the same column, normalise the units under the reporting rules before summing.
Sum across a date window
Suppose column A contains transaction dates or timestamps, column D contains amounts, G2 contains the first calendar date and H2 contains the last calendar date. To include the whole last day, use an inclusive start and an exclusive boundary at the beginning of the next day.
=SUMIFS(D2:D100,A2:A100,">="&G2,A2:A100,"<"&(H2+1),C2:C100,"Approved")The selectors in this example should hold actual whole-day date values. A transaction at 5 pm on the end date can pass the second condition, while a transaction at midnight the next day is excluded. Test both. If your selectors include times, establish the actual interval you intend before using this pattern.
Choose the date column that answers the question
A table may have a request date, approval date and payment date. An approval report should usually use the approval date, while a cash report may use payment date. Check the brief and inspect two records where those dates differ. A plausible-looking total can be calculated from the wrong date column.
Test a month transition
Use a record late on the last day of the month and a record early on the first day of the next month. Verify that only the former appears in the intended total. If data comes from another system, inspect what its timestamps represent and what dates the sheet displays. Do not rely on visual grouping alone.
Distinguish blank dates and real dates
A missing date should not be treated as an ordinary scheduled or completed record. Use an explicit rule where appropriate and test one blank. A value displayed like a date may be text. Check the data type before investigating the formula’s operators.
Understand AND and OR before adding totals together
Separate SUMIFS criteria are AND conditions. A row must pass all of them. For an OR across two mutually exclusive statuses, you can calculate the two subtotals and add them. This example assumes one status per record.
=SUMIFS(D2:D100,C2:C100,"Approved")+SUMIFS(D2:D100,C2:C100,"Paid")If the two groups can overlap, adding their subtotals can count one record twice. For example, “Art or approved” can match the same row in both groups. Use a verified helper flag or another suitable method that includes each row once. Compare the result with a small list where you know which rows overlap.
Use a helper when eligibility is hard to explain
A helper column can make each row’s eligibility explicit. Review the helper on an included row, an excluded row and a row that satisfies both possible rules. Then sum the amounts using the flag and any additional criteria. Give the helper a clear heading so another editor can review the actual business rule.
Keep statuses and amounts in separate fields
A single cell with several labels can make equality criteria unreliable for a status summary. If the dataset intentionally records multiple tags, decide how eligibility is represented. Use a structured table or an explicit helper where appropriate. Check the result against the actual matching records rather than assuming a partial-text criterion means the right thing.
Explain a result of zero
Zero can mean that no record matched. It can also mean matching amounts sum to zero, such as a charge of 12 and an offset of -12. Check the count of matching rows to distinguish those cases. A count and an amount total answer different questions.
=COUNTIFS(C2:C100,"Approved",B2:B100,"Art")Compare this count with the SUMIFS total for the same filters. A nonzero count and zero total can be valid. If all matching rows contain missing or nonnumeric amounts, review the source before presenting the total. Keep control counts alongside important reports where that helps interpretation.
Find the records behind a surprising result
Use a separate filter or filter view with the same conditions. Inspect the exact matching rows, add a few values manually and compare the expected result. Verify that refunds or adjustments have the intended signs. If the report totals the same import twice, inspect duplicate keys before changing the formula.
Check unexpected exclusions
A status might contain a trailing space. A club selector may refer to an earlier spelling. The amount may lie below the last included row. Inspect the actual values and ranges. Correct the source or update the report under the approved workflow, then check the amount and count again.
Choose between SUMIFS and a pivot table
SUMIFS is useful when you need a particular metric in a particular report cell, such as approved spending for the selected club and dates. A pivot table is useful when you need to compare several clubs and statuses at once. Both require clean source records and a clear definition of the amount.
A useful control is to compare one pivot subtotal with a SUMIFS calculation using the same rows and conditions. If the totals differ, check the source range, filters, data types and any duplicate imports. Also verify that the pivot uses SUM and not COUNT. A cross-check only works when the two reports are asking the same question.
Verify a total before copying it into another report
Check one known included record, one excluded record and a boundary record. Recalculate a small subset by hand. Inspect the last included row and test the current selector. If the recipient needs a fixed record, make an approved snapshot after verifying the live total. Note the date window and criteria where the process allows.
Recheck after a new import
Confirm that the source columns still mean the same thing and that numeric values retained their types. Check for repeated rows or adjustments. Verify the newest record is inside the ranges. Do not change the criteria simply because an expected amount is missing until you have found what happened to that record.
Build a summary grid you can copy across and down
Put club names in F3:F8 and status labels in G2:I2. Start the formula in G3. Lock the source ranges. Lock the club label’s column so it stays in F when the formula is copied across. Lock the status label’s row so it stays in row two when copied down.
=SUMIFS($D$2:$D$100,$B$2:$B$100,$F3,$C$2:$C$100,G$2)Copy it across and down. Inspect the bottom-right formula and make sure the source ranges stayed fixed. The club should refer to the current row in column F. The status should refer to the current column in row two. Compare two diagonally separated cells with a manual total, because checking only the first row can miss a reference problem.
Verify the groups and the grand total
If each row belongs to exactly one listed club and one listed status, the grid can be compared with the overall amount for those same records. If some statuses are intentionally omitted, the grid will not equal the full source. Record which groups belong in the control check. Inspect any blank club or unfamiliar status separately so its absence is deliberate.
Keep notes outside the amount range
A working ledger can have subtotal rows or notes mixed with data. Use a source range that contains only the intended records. If a numeric subtotal is left in the amount column and shares a matching category, it can be added again. Verify the source shape and one known entry near the bottom.
Give a copied summary enough room for updates
If a new club or status appears, decide whether the grid should include it. A formula can correctly calculate all existing labels while leaving an unlisted category out of the report. Add the approved category and verify its total. A pivot can be more convenient when many categories change frequently. A fixed grid can be easier to integrate into a designed report.
Match text carefully when a criterion contains punctuation
For a normal status such as Approved, a direct criterion is usually clear. Google documents wildcard use in conditional criteria. A question mark or asterisk in a criterion can match a pattern. If those symbols are literal parts of a code, use the appropriate escaping or an exact comparison workflow. Test a real code containing the symbol and a similar code that should not match.
Use partial matches only when they reflect the rule
A criterion such as “Art*” can be useful when the report intentionally includes several labels that begin with Art. It might also include a label you did not intend. Before using a wildcard, list the eligible categories and test one that looks similar but should stay out. A maintained set of labels or helper flag may be clearer for an important report.
Confirm what the selector cell actually contains
A dropdown can reduce typing variations, but pasted values or older records may still differ. Compare a known source value with the selector. If labels have been renamed, check both the old and new data. Correct approved source labels before using a broad partial match to hide inconsistencies. That keeps later reports easier to interpret.
Keep identifier and amount types distinct
A numeric amount should support arithmetic. A code made of digits can still be an identifier that needs to remain text. Verify the source specification before coercing a column. For example, an expense amount of 42 and a reference code of 0042 have different roles. Check the actual values and the report’s matching logic after any import cleanup.
Reconcile positive and negative entries
A ledger can contain purchases, refunds and corrections. Decide which values should be netted and which should be shown separately. For a net report, use the sign convention the source requires. For gross spending, the relevant scope may exclude adjustments or present them separately. Verify a positive and negative entry by reading the source record.
Work through a refund
Suppose Art has two approved amounts, 12 and 7, and an approved refund of -4. With all three records in scope, the net amount is 15. A report showing 19 may be summing only positive entries or omitting the refund row. Check the criteria and source range before deciding which number is correct. The required measure determines the result.
Avoid treating a net zero as no activity
Two records can offset each other exactly. Show an activity count or a separate note where the distinction matters. Compare the sum with the count using the same group and date criteria. If the count is positive and the sum is zero, inspect the amounts. A dashboard with both measures helps readers interpret the result.
Check an adjustment with a different status
A refund might still be Pending while the original expense is Approved. A total restricted to Approved should exclude the pending adjustment until the source says otherwise. If the report is meant to show anticipated net spending, use its actual criteria and label it accordingly. Avoid mixing the two measures under one heading.
Use an OR condition without counting a shared record twice
Take a small register with Art Approved 12, Art Pending 8 and Science Approved 15. If the rule is “Art or Approved,” all three records qualify and the total is 35. Adding the Art total of 20 to the Approved total of 27 would give 47 because Art Approved 12 was counted in both groups. Use a helper to mark each qualifying row once.
Make the helper express the exact rule
In a spare column for this example, enter a row-level Boolean test such as =OR(B2="Art",C2="Approved") and fill it down. Then sum amounts where the helper is TRUE. Check that the overlap row is marked once and that an unrelated row is excluded. Add any date or other required criteria to the final sum. Keep the helper out of the sum range.
Review the scope of any extra condition
If the report says “within this month and either Art or Approved,” the date condition applies to both alternatives. If you only apply it to one subtotal, the total can include older records unexpectedly. Test an Art record outside the month and an Approved record outside the month. Neither should appear. This is a useful check for ambiguous wording as well as for formulas.
Check a date report at its edges
Use a small test dataset with four records: just before the start, exactly at the start, late on the last day and exactly at midnight on the next day. Use whole-date selectors and the half-open interval formula. The middle two records should be included. Check the result before changing the formula to address a larger dataset.
Verify the source’s date and time interpretation
A timestamp imported as text can look like a date and compare differently. Check the imported type and the intended time zone or reporting convention. If the report counts local calendar days, verify a record near midnight under that convention. Use the approved source transformation and keep the original available during review.
Add parentheses around the next-day calculation if helpful
Writing the exclusive criterion as "<"&($H$2+1) can make the intended evaluation clearer to the next editor. Make sure H2 contains the end date rather than the number of days or the start date. Test the last included day and the first excluded timestamp. A clear boundary is easier to maintain when the worksheet changes hands.
Prepare a control check for a shared report
Write the measure and its scope in ordinary language. For example, “Net approved amounts for Art recorded between 1 and 30 June, inclusive.” Compare that statement with the criteria pairs. Check the amount column and the date column. Then verify a small subset manually. A formula that matches the wording is easier to audit than a number with no label.
Inspect matching records before exporting
Use an approved filter on the same source, or inspect the rows directly. Match the club, status and dates to the formula. Check the first and last date and a row excluded by status. Verify any total against a second approved calculation. If the report is fixed, preserve its criteria and accepted output where the process allows.
Recheck after a source correction
If a row changes status or amount, verify that the summary moves by the expected difference. If an approved 7 becomes pending, the approved total should fall by 7. If a new row arrives outside the current range, extend the calculation deliberately and test it. Keep the report tied to the intended data rather than making manual changes to the displayed result.
Practise reviewing another person’s formula
Read its first argument, then each pair of criteria. Explain which rows contribute and why. Check whether references are locked for copying and what happens for a missing selector. Compare one example with a manual total. These checks help you identify the purpose of an existing formula before changing it.
Use SUMIF, SUMIFS and a row filter for different questions
A one-condition SUMIF can answer a simple category total. SUMIFS allows several criteria and uses the sum range first. If you are adapting an older formula, check its argument order before copying it into a new one. A filter returns rows to inspect. A sum returns the amount across the rows that qualify. Use both when the reader needs a number and a way to verify it.
Start from the measure rather than the function name
If a report asks for how many Art expenses are approved, use a count. If it asks for the total amount, use the amount column with SUMIFS. If it asks which expenses are approved, use a filter. A total of 19 and a count of two can both be correct for the same source. Give the outputs labels that say which question they answer.
Check a real total with a separate list
Filter the source to the same club and status. Read the resulting rows and compare the amounts with your summary. If the filtered view includes a record outside the formula’s source range, extend or correct the formula. If a manually counted row has a different status from the criterion, confirm which status is correct. The comparison should use the same exact definition.
Use grouping when many categories are needed
A pivot table can summarise several clubs and statuses together. SUMIFS can calculate a specific figure within a designed report. If you use both, reconcile one subtotal from the same dates and fields. Check any subtotals and adjustments in the original records before trusting a discrepancy between the two views.
Troubleshoot a result that changes after copying the formula
Inspect the copied formula and compare it with the original. Look at which references moved. A criteria range can shift when it should stay fixed, or a selector can stay locked when it should change by row. A report grid often needs a mix of fixed source ranges and partially fixed label references. Check the top-left and bottom-right cells.
A new column uses the wrong criterion
If the report headers show Approved and Paid, the formula should use the header above the current result cell. A fully locked reference to the first header will keep using Approved in every column. Use the appropriate mixed reference and verify a group whose statuses have different totals. Identical totals in a test group can hide the problem.
A new row uses the wrong club
A copied formula should take its club from the row it occupies. If it always reads the first club, check whether the label’s row was locked. Use a test with two clubs and different amounts. Verify that the label and result move together. Keep the data ranges fixed if they refer to one master table.
Two reports have different amounts for the same label
Compare the date range, criteria and source. One may use Approved and the other Paid. One may include refunds or records outside the other range. Read the actual formulas and inspect matching rows. Correct the report label if the measures are intentionally different. Change the calculation only if its definition is wrong.
Practise with a short ledger that exposes edge cases
Use a harmless table of five records. Include an approved amount of 12, an approved refund of -4, a pending amount of 8 for the same club, an approved amount of 15 for a different club, and a record on the first day after the report window. Predict the subtotal before writing the formula. If the rule is approved net amount for the first club within the window, the first two records give 8.
Change one condition and explain the difference
Remove the club criterion and identify the additional approved record. Restore it and include Pending through a verified branch if the new task asks for both statuses. Check whether the date restriction still applies. Work through one change at a time so you can explain exactly why the total moved.
Write the result with its scope
A number copied into another report needs a clear label. State the period, the status and whether the amount is net or gross where that matters. Check the value after changing any selector. For an accepted output, preserve the approved snapshot or verification record as required.
Check whether the data is already summarised
A source can contain daily totals and individual transactions. If you sum both, the report can count the same activity twice. Identify what each row represents before choosing the range. Use transaction records when the calculation should add each transaction once. Use a daily summary only when it contains the approved figures and the criteria can be applied at that level. Check one day by comparing the detailed records with its summary.
If the file contains currency, time or quantity, confirm that the units belong together. Format the output only after checking the actual number. Keep a small test total for the next import so you can detect a changed column or a repeated summary row. A result that matches the previous month’s scale can still be wrong if its source mixed two levels of detail.
Frequently asked questions
Does SUMIFS require all conditions to be true?
Yes. Each criteria pair is another condition the row must pass.
Which range goes first?
Put the range of amounts to sum first, then add criteria range and criterion pairs.
How do I use a criterion from a cell?
Use its cell reference. For an operator and a threshold cell, join the quoted operator and reference with an ampersand.
Why is the total zero?
Check matching rows and amount types. Zero may mean no matches, a net zero or missing numeric values.
Can I use dates with SUMIFS?
Yes. Use actual date values and verify both boundaries, especially when the source stores times.
Can I add two SUMIFS results for OR?
Only when you account for overlaps. Mutually exclusive groups can be added directly.
Can I use SUMIFS to build a grid by club and status?
Yes. Lock the data ranges and use mixed references for the club and status labels. Check cells in different rows and columns.
How do I sum records later on the end date?
When the end selector is a whole date, use an exclusive upper boundary at the beginning of the next day. Check a late timestamp and a record just outside the range.
Helpful reading
- Google Docs Editors Help on SUMIFS
- How to Use FILTER in Google Sheets
- How to Make a Dropdown List in Google Sheets
- How to Use XLOOKUP in Google Sheets
- How to Create a Pivot Table in Google Sheets
- How to Add and Count Checkboxes in Google Sheets
Check which rows contributed to the total
Use aligned ranges and clear criteria. Verify a matching record and the boundary before relying on the result.