Lesson 8: Define criteria precisely
Assume A2:A100 contains region, B2:B100 contains dates, C2:C100 contains sales rep, and D2:D100 contains amounts. Keep criteria ranges the same size as the sum range.
Sum sales for the West region: =SUMIF(A2:A100,"West",D2:D100)
Conditional and database formula reference
23 formulas shown
| Function | Purpose | Example |
|---|---|---|
| SUMIF | Sums using one condition. | =SUMIF(A2:A100,"West",D2:D100). |
| SUMIFS | Sums using multiple AND conditions. | =SUMIFS(D2:D100,A2:A100,"West",C2:C100,"Ava"). |
| COUNTIF | Counts matches to one condition. | =COUNTIF(D2:D100,">1000"). |
| COUNTIFS | Counts rows meeting multiple conditions. | =COUNTIFS(A2:A100,"West",D2:D100,">=1000"). |
| AVERAGEIF | Average for rows meeting one condition. | =AVERAGEIF(A2:A100,"West",D2:D100). |
| AVERAGEIFS | Average for rows meeting multiple conditions. | =AVERAGEIFS(D2:D100,A2:A100,"West",C2:C100,"Ava"). |
| MAXIFS / MINIFS | Largest or smallest value matching conditions. | =MAXIFS(D2:D100,A2:A100,"West"). |
| SUMPRODUCT | Flexible array aggregation; can combine Boolean criteria. | =SUMPRODUCT((A2:A100="West")*D2:D100). |
| SUBTOTAL | Calculates summaries that can ignore filtered or hidden rows. | =SUBTOTAL(9,D2:D100) sums visible filtered rows. |
| AGGREGATE | Runs a selected aggregate and can ignore errors/hidden rows. | =AGGREGATE(9,6,D2:D100) sums while ignoring errors. |
| DSUM | Sums a database field using a criteria range. | =DSUM(A1:D100,"Amount",F1:F2). |
| DCOUNT / DCOUNTA | Counts numeric or nonblank database records matching criteria. | =DCOUNT(A1:D100,"Amount",F1:F2). |
| DAVERAGE | Averages matching database records. | =DAVERAGE(A1:D100,"Amount",F1:F2). |
| DMAX / DMIN | Largest or smallest matching database field value. | =DMAX(A1:D100,"Amount",F1:F2). |
| DGET | Returns one matching database record; errors on zero or multiple. | =DGET(A1:D100,"Amount",F1:F2). |
| DPRODUCT | Multiplies matching field values. | =DPRODUCT(A1:D100,"Amount",F1:F2). |
| DSTDEV / DSTDEVP | Sample or population standard deviation for matching records. | =DSTDEV(A1:D100,"Amount",F1:F2). |
| DVAR / DVARP | Sample or population variance for matching records. | =DVAR(A1:D100,"Amount",F1:F2). |
| COUNTBLANK | Counts blank cells in a range. | =COUNTBLANK(D2:D100). |
| COUNT | Counts numeric values, not text numbers. | =COUNT(D2:D100). |
| COUNTA | Counts nonblank cells, including formulas returning empty text. | =COUNTA(A2:A100). |
| MAX / MIN | Maximum and minimum across numeric values. | =MAX(D2:D100). |
| AVERAGE | Mean of numeric values. | =AVERAGE(D2:D100). |
Criteria tips and date ranges
Criteria such as ">=1000" are text expressions. For a comparison stored in another cell, join the operator and value, such as ">="&F2. For a month total, use a start date and an exclusive next-month bound with SUMIFS. In database functions, criteria headers must match database field names exactly.
Progress stays in this browser.