FormulaCraft / Spreadsheet notes
200 practical notes. A copyable formula, a concrete result and one thing to check. Get one short tip each week.
Optional and confirmed by email. Unsubscribe anytime.
200 notes. English formulas and comma separators; regional settings may differ.
Conditional totals / 001
Use this when you need to add revenue for one region.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIF(A1:A3,"West",B1:B3)Expected result: 650
One thing to check: Keep the criteria range and sum range aligned.
Excel 2016 or later
Conditional totals / 002
Use this when you need to total only paid sales.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIF(C1:C3,"Paid",B1:B3)Expected result: 650
One thing to check: Use consistent status labels; an extra space changes a match.
Excel 2016 or later
Conditional totals / 003
Use this when you need to sum everything except one region.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIF(A1:A3,"<>West",B1:B3)Expected result: 100
One thing to check: Blank labels also meet this not-equal condition.
Excel 2016 or later
Conditional totals / 004
Use this when you need to total sales above a threshold.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIF(B1:B3,">200",B1:B3)Expected result: 650
One thing to check: Numeric-looking text should be converted to real numbers first.
Excel 2016 or later
Conditional totals / 005
Use this when you need to combine region and payment filters.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIFS(B1:B3,A1:A3,"West",C1:C3,"Paid")Expected result: 650
One thing to check: SUMIFS starts with the sum range, unlike SUMIF.
Excel 2016 or later
Conditional totals / 006
Use this when you need to sum a value band with two boundaries.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIFS(B1:B3,B1:B3,">=100",B1:B3,"<=250")Expected result: 350
One thing to check: Both boundaries are included here.
Excel 2016 or later
Conditional totals / 007
Use this when you need to exclude a status from a total.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIFS(B1:B3,C1:C3,"<>Unpaid")Expected result: 650
One thing to check: Decide how blank statuses should be treated.
Excel 2016 or later
Conditional totals / 008
Use this when you need to add two regions without a helper column.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIF(A1:A3,"West",B1:B3)+SUMIF(A1:A3,"East",B1:B3)Expected result: 750
One thing to check: Do not add overlapping criteria or you will double-count.
Excel 2016 or later
Conditional totals / 009
Use this when you need to measure the unpaid balance.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=SUMIF(C1:C3,"Unpaid",B1:B3)Expected result: 100
One thing to check: This totals labels; it does not validate your accounting records.
Excel 2016 or later
Conditional totals / 010
Use this when you need to average sales for a selected region.
A contains region, B contains revenue and C contains payment status. Start the sample in A1, without a header.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=AVERAGEIF(A1:A3,"West",B1:B3)Expected result: 325
One thing to check: A region with no numeric matches returns an error.
Excel 2016 or later
Counting / 011
Use this when you need to count rows that match a region.
A is region, B is revenue and C is status. Put these three sample rows in A1:C3.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=COUNTIF(A1:A3,"West")Expected result: 2
One thing to check: COUNTIF is not case-sensitive.
Excel 2016 or later
Counting / 012
Use this when you need to count only paid records.
A is region, B is revenue and C is status. Put these three sample rows in A1:C3.
| Row | A | B | C |
|---|---|---|---|
| 1 | West | 250 | Paid |
| 2 | East | 100 | Unpaid |
| 3 | West | 400 | Paid |
=COUNTIF(C1:C3,"Paid")Expected result: 2
One thing to check: Use a controlled status list to prevent spelling variations.
Excel 2016 or later
Check each example in your workbook before using it on real data. Function reference: Microsoft Excel documentation.