Mastering SUMIFS for Accounting
In accounting, we rarely sum everything in a column. We sum based on criteria—like "Total all rows where the Account is Cash." SUMIFS is the tool for this job.
The Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, ...)
- sum_range: The column with the dollars (usually the Amount column).
- criteria_range1: The column you are checking (usually the Account Name column).
- criteria1: The specific account you want to total (e.g., "Cash").
A Real-World Example
Suppose you have a general ledger in columns A through C:
| Date | Account | Amount |
|---|---|---|
| Jan 1 | Cash | 1000.00 |
| Jan 2 | Inventory | 500.00 |
| Jan 3 | Cash | 250.00 |
To find the total for Cash, your formula would be:
=SUMIFS(C2:C4, B2:B4, "Cash")
Excel looks at the range B2:B4, finds the rows that say "Cash", and adds up the corresponding values in column C (1000 + 250 = 1250).
Why use SUMIFS instead of SUMIF?
The "S" at the end of SUMIFS allows for multiple criteria. For example, you can sum only "Cash" transactions that happened in "January":
=SUMIFS(Amount, Account, "Cash", Month, "January")
We recommend always using SUMIFS (even for one criterion) because it keeps your logic consistent and expandable.
Common Mistakes
- Swapping Ranges: In SUMIFS, the
sum_rangecomes first. In the older SUMIF, it comes last. If your total is 0, check your range order. - Exact Matches: If your criteria is "Cash", it won't match "Cash ". Watch out for trailing spaces in your ledger.
- Absolute References: When dragging a SUMIFS formula down a trial balance, use dollar signs (e.g.,
$C$2:$C$100) so your source ranges don't shift.