Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
SUMIF and AVERAGEIF
It looks down the Store column, and wherever it finds Lagos, adds that row's revenue.
Try this lesson free →Example
SUMIF checks one column and adds up another:
=SUMIF(C2:C9,"Lagos",F2:F9)| Order | Day | Store | Product | Qty | Revenue | Report | Answer | |
|---|---|---|---|---|---|---|---|---|
| O101 | Mon | Lagos | Earbuds | 2 | 70 | Lagos revenue | ||
| O102 | Mon | Abuja | Charger | 1 | 15 | Earbuds revenue | ||
| O103 | Tue | Lagos | Phone case | 3 | 24 | Abuja revenue | ||
| O104 | Wed | Lagos | Earbuds | 1 | 35 | Chargers sold | ||
| O105 | Wed | Abuja | Earbuds | 4 | 140 | Avg Lagos order | ||
| O106 | Thu | Lagos | Charger | 2 | 30 | Avg Earbuds order | ||
| O107 | Fri | Abuja | Phone case | 5 | 40 | |||
| O108 | Fri | Lagos | Earbuds | 3 | 105 |
Key points
C2:C9First, the range to check: the Store column.
"Lagos"Second, what to look for.
F2:F9Last, the range to add up: Revenue.
Put together: =SUMIF(C2:C9,"Lagos",F2:F9). AVERAGEIF has the same shape but averages instead.
Practice
1. In I2, total the revenue from Lagos.
=SUMIF(C2:C9,"Lagos",F2:F9)2. In I3, total the revenue from Earbuds.
=SUMIF(D2:D9,"Earbuds",F2:F9)3. In I7, find the average Earbuds order value. AVERAGEIF has the same shape as SUMIF: the range to check, what to look for, then the range to average.
=AVERAGEIF(D2:D9,"Earbuds",F2:F9)4. In I4, total the revenue from Abuja.
=SUMIF(C2:C9,"Abuja",F2:F9)5. In I5, total how many Chargers were sold. Add up the Qty column this time.
=SUMIF(D2:D9,"Charger",E2:E9)Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →