80 formula exercises on three real datasets, each with its answer and the formula that gets there. Download a workbook, try each exercise in an empty cell, then open the answer to check your work. Every set is also an interactive lesson on LogicExcel, which marks your formula as you type.
- Download the workbook for the set you want (links below). It opens in Excel, Google Sheets or LibreOffice.
- Type the formula for each exercise in any empty cell of the data sheet.
- Open Show the answer to compare. The formula shown is one way to get there; any formula that gives the same answer is right.
| Workbook | What is in it | Exercises | Source |
|---|---|---|---|
| AppleSegmentRevenue.xlsx | Apple revenue by product | SUM | Apple Inc. 10-K filings |
| WorldCropProduction2018.xlsx | World crop production, 2018 | COUNT, COUNTIF and COUNTIFS; MIN, MAX, LARGE and SMALL; VLOOKUP; INDEX and MATCH | FAOSTAT (FAO), 2018 |
| NewYorkTrout.xlsx | New York trout stocking | SUMIF; SUMIFS; LEFT, RIGHT, LEN and MID | New York State, via data.gov |
- SUM (10 exercises)
- COUNT, COUNTIF and COUNTIFS (10 exercises)
- MIN, MAX, LARGE and SMALL (10 exercises)
- VLOOKUP (10 exercises)
- INDEX and MATCH (10 exercises)
- SUMIF (10 exercises)
- SUMIFS (10 exercises)
- LEFT, RIGHT, LEN and MID (10 exercises)
Workbook: AppleSegmentRevenue.xlsx. Interactive version, marked as you type: Lesson 55: SUM Function.
1. All functions in Excel start with the = sign. The function inputs, or arguments, go inside the parenthesis and are separated by commas. Type =SUM(E5, F5, G5) into the formula bar to get the sum of iPhone sales for all three years.
Show the answer
$445,050
=SUM(E5, F5, G5)
2. You can also supply a range of cells as the argument to sum. The range E5:E9 tells Excel "Every cell from E5 to E9." Type =SUM(E5:E9) into the formula bar to find total sales for the year 2020.
Show the answer
$274,515
=SUM(E5:E9)
3. Use the SUM function with the argument E6:G6 to get the total Mac sales across the three years.
Show the answer
$79,560
=SUM(E6:G6)
4. Now you want to calculate the sum of sales for all products across all three years. In the formula bar, use the SUM function with the argument E5:G9.
Show the answer
$800,284
=SUM(E5:G9)
5. Now your boss tells you that 2020 sales were under-reported by $158. Change your formula in the formula bar so that it adds 158 to the total. Your formula should look like this: =SUM(E5:E9, 158).
Show the answer
$274,673
=SUM(E5:E9, 158)
6. Use SUM to find the total sales for 2019.
Show the answer
$260,174
=SUM(F5:F9)
7. Use SUM to find the total sales for 2018.
Show the answer
$265,595
=SUM(G5:G9)
8. Use SUM to find the total iPad sales across all three years.
Show the answer
$63,384
=SUM(E7:G7)
9. Use SUM to find the total sales of Wearables, Home and Accessories across the three years.
Show the answer
$72,483
=SUM(E8:G8)
10. Use SUM to find the total sales of Services across the three years.
Show the answer
$139,807
=SUM(E9:G9)
Workbook: WorldCropProduction2018.xlsx. Interactive version, marked as you type: Lesson 56: COUNT Practice.
1. The workbook shows crop production by country for 2018. You want to know the number of countries for which we have banana production data. The COUNT function counts the number of numerical values in a range of cells and will ignore text, so you can write the following function to count the number of countries for which we have banana production data: =COUNT(B:B) Try typing the formula into cell F2. What is the result of the formula?
Show the answer
119
=COUNT(B:B)
2. Now you want to know the number of countries for which we have Coffee Bean production data. Use the COUNT function to find the answer.
Show the answer
77
=COUNT(C:C)
3. Now you want to know the number of countries which produce more than 800 tonnes of bananas. Use the following formula to find the answer: =COUNTIF(B:B, ">800")
Show the answer
106
=COUNTIF(B:B, ">800")
4. Now you want to know the number of countries which produce more than 100 tonnes of avocados.
Show the answer
64
=COUNTIF(D:D, ">100")
5. Now you want to know the number of countries for which we have data which produce LESS than 100 tonnes of coffee beans. Use COUNTIF to find the answer.
Show the answer
11
=COUNTIF(C:C, "<100")
6. Now you want to know the number of countries which produce more than 100 tonnes of bananas AND less than 100 tonnes of avocados. Use the following formula to find the answer: =COUNTIFS(B:B, ">100", D:D, "<100")
Show the answer
3
=COUNTIFS(B:B, ">100", D:D, "<100")
7. How many countries produce more than 200 tonnes of bananas and less than 200 tonnes of coffee beans?
Show the answer
14
=COUNTIFS(B:B, ">200", C:C, "<200")
8. How many countries produce more than 200 tonnes of coffee beans and have data on avocado production? Hint: The value in column D is not equal to "No Data".
Show the answer
42
=COUNTIFS(C:C, ">200", D:D, "<>No Data")
9. How many countries produce more than 500 tonnes of avocados?
Show the answer
58
=COUNTIFS(D:D, ">500")
10. How many countries produce more than 500 tonnes each of bananas, coffee beans, and avocados?
Show the answer
33
=COUNTIFS(B:B, ">500", C:C, ">500", D:D, ">500")
Workbook: WorldCropProduction2018.xlsx. Interactive version, marked as you type: Lesson 57: MIN, MAX, LARGE, SMALL Practice.
1. The MIN and MAX functions find minimum and maximum values from a range of cells. Use =MIN(B:B) to find the minimum number for banana production.
Show the answer
5
=MIN(B:B)
2. Use MAX to find the maximum number of tonnes of coffee beans produced.
Show the answer
3,552,729
=MAX(C:C)
3. The LARGE and SMALL functions find the nth largest or nth smallest value from a range of cells. For example, =LARGE(B:B, 3) would return the 3rd largest value of banana production. Use the SMALL function to find the 4th smallest value in avocado production.
Show the answer
58
=SMALL(D:D, 4)
4. Find the 10th largest value of coffee bean production.
Show the answer
240,400
=LARGE(C:C, 10)
5. Find the 100th largest value of banana production.
Show the answer
2,762
=LARGE(B:B, 100)
6. Find the 3rd smallest value of banana production.
Show the answer
16
=SMALL(B:B, 3)
7. Find the 2nd smallest value of avocado production.
Show the answer
20
=SMALL(D:D, 2)
8. Find the 25th largest value of coffee bean production.
Show the answer
43,319
=LARGE(C:C, 25)
9. Find the 60th largest value of avocado production.
Show the answer
431
=LARGE(D:D, 60)
10. Find the 5th smallest value of coffee bean production.
Show the answer
13
=SMALL(C:C, 5)
Workbook: WorldCropProduction2018.xlsx. Interactive version, marked as you type: Lesson 58: VLOOKUP Practice.
1. The VLOOKUP function has 4 arguments: 1. The value you know. 2. The range to search. 3. How many columns to look to the right. 4. Whether to use approximate match (this will be false if looking for an exact match). In the formula bar, use the following formula to find the banana production of Paraguay: =VLOOKUP("Paraguay", A:B, 2, FALSE)
Show the answer
75,868
=VLOOKUP("Paraguay", A:B, 2, FALSE)
2. Modify your formula to find the banana production of Solomon Islands.
Show the answer
316
=VLOOKUP("Solomon Islands", A:B, 2, FALSE)
3. Use VLOOKUP to find the number of tonnes of coffee beans produced by Ghana.
Show the answer
736
=VLOOKUP("Ghana", A:C, 3, FALSE)
4. You know a certain country produced 3,295 tonnes of bananas, but you don't know which country. Use VLOOKUP to find the number of tonnes of coffee beans that country produced.
Show the answer
720
=VLOOKUP(3295, B:C, 2, FALSE)
5. Use VLOOKUP to find the number of tonnes of coffee beans produced by Cambodia.
Show the answer
357
=VLOOKUP("Cambodia", A:C, 3, FALSE)
6. Use VLOOKUP to find the number of tonnes of avocados produced by Kenya.
Show the answer
233,933
=VLOOKUP("Kenya", A:D, 4, FALSE)
7. Use VLOOKUP to find the number of tonnes of avocados produced by the country that produced 370 tonnes of coffee beans.
Show the answer
1,035
=VLOOKUP(370, C:D, 2, FALSE)
8. Use VLOOKUP to find the number of tonnes of avocados produced by the country that produced 66,381 tonnes of bananas.
Show the answer
2,456
=VLOOKUP(66381, B:D, 3, FALSE)
9. Use VLOOKUP to find the number of tonnes of coffee beans produced by Belize.
Show the answer
90
=VLOOKUP("Belize", A:C, 3, FALSE)
10. Use VLOOKUP to find the number of tonnes of coffee beans produced by Fiji.
Show the answer
13
=VLOOKUP("Fiji", A:C, 3, FALSE)
Workbook: WorldCropProduction2018.xlsx. Interactive version, marked as you type: Lesson 59: INDEX MATCH Practice.
1. The MATCH function finds the position of a value in a range of cells. To find the position of Fiji in column A, enter the following formula in the formula bar: =MATCH("Fiji", A:A, 0) The final 0 in the formula just tells Excel that we're looking for an exact match.
Show the answer
43
=MATCH("Fiji", A:A, 0)
2. In the formula bar, enter =MATCH("Banana", 1:1, 0) to get the position of the label "Banana" in row 1.
Show the answer
2
=MATCH("Banana", 1:1, 0)
3. The INDEX function returns the value from a cell in a grid, as specified first by the vertical position, then horizontal. To return the number of tonnes of avocados produced by Angola, write the following formula in the formula bar: =INDEX(A:D, 3, 4) because we're looking in the 3rd row from the top, and the 4th column from the left. What does the formula return?
Show the answer
50,087
=INDEX(A:D, 3, 4)
4. Now in your formula in F4, replace 3 with MATCH("Angola", A:A, 0) and replace 4 with MATCH("Avocado", 1:1, 0). What does the formula return?
Show the answer
50,087
=INDEX(A:D, MATCH("Angola", A:A, 0), MATCH("Avocado", 1:1, 0))
5. To find the number of tonnes of Coffee Beans produced by Tonga, simply replace "Angola" with "Tonga" and replace "Avocado" with "Coffee Bean". What does the formula return?
Show the answer
15
=INDEX(A:D, MATCH("Tonga", A:A, 0), MATCH("Coffee Bean", 1:1, 0))
6. How many tonnes of Bananas did Spain produce?
Show the answer
386,230
=INDEX(A:D, MATCH("Spain", A:A, 0), MATCH("Banana", 1:1, 0))
7. How many tonnes of Coffee Beans did Nepal produce?
Show the answer
513
=INDEX(A:D, MATCH("Nepal", A:A, 0), MATCH("Coffee Bean", 1:1, 0))
8. How many tonnes of Bananas did Zambia produce?
Show the answer
702
=INDEX(A:D, MATCH("Zambia", A:A, 0), MATCH("Banana", 1:1, 0))
9. How many tonnes of Avocados did Panama produce?
Show the answer
8,358
=INDEX(A:D, MATCH("Panama", A:A, 0), MATCH("Avocado", 1:1, 0))
10. How many tonnes of Bananas did Australia produce?
Show the answer
373,931
=INDEX(A:D, MATCH("Australia", A:A, 0), MATCH("Banana", 1:1, 0))
Workbook: NewYorkTrout.xlsx. Interactive version, marked as you type: Lesson 60: SUMIF Practice.
1. The workbook shows trout populations in different bodies of water in New York. You're planning a trip to Westerlo, New York, and as an avid trout enthusiast you want to know the total number of trout in the town. The SUMIF function has three arguments: the criteria range and the criteria, and the optional third argument is the range to be summed. Type this into the formula bar: =SUMIF(B:B, "Westerlo", D:D)
Show the answer
283
=SUMIF(B:B, "Westerlo", D:D)
2. You know you may not stay in Westerlo for your whole trip, so you're interested in the trout totals for the entire county. Modify your SUMIF formula to count the number of trout when the county is "Albany".
Show the answer
6,399
=SUMIF(A:A, "Albany", D:D)
3. Now you're only interested in rainbow trout and want to know how many rainbow trout are in all of New York. Find the sum when species name is "Rainbow Trout" (round to the nearest whole trout).
Show the answer
450,415 (the exact total is 450,414.85)
=SUMIF(E:E, "Rainbow Trout", D:D)
4. How many trout are in Allen Lake?
Show the answer
4,280
=SUMIF(C:C, "Allen Lake", D:D)
5. You decide you only want to fish in creeks. Use wildcard matching to find the total number of trout in bodies of water that end in "Creek". Hint: Waterbody name is equal to "*Creek".
Show the answer
458,103
=SUMIF(C:C, "*Creek", D:D)
6. How many trout are in rivers?
Show the answer
394,012
=SUMIF(C:C, "*River", D:D)
7. How many trout are in ponds (round to the nearest whole trout)?
Show the answer
57,400 (the exact total is 57,400.05)
=SUMIF(C:C, "*Pond", D:D)
8. How many trout are in the town of Aurora?
Show the answer
2,209
=SUMIF(B:B, "Aurora", D:D)
9. How many trout are in Erie county?
Show the answer
14,011
=SUMIF(A:A, "Erie", D:D)
10. How many trout are in Ten Mile River?
Show the answer
5,085
=SUMIF(C:C, "Ten Mile River", D:D)
Workbook: NewYorkTrout.xlsx. Interactive version, marked as you type: Lesson 61: SUMIFS Practice.
1. For the SUMIFS function, the first argument is the range of numbers to be summed, and the remaining arguments are all pairs of cell ranges and criteria. For example, to find the total number of Brown Trout in Albany county, try typing the following formula into the formula bar: =SUMIFS(D:D, A:A, "Albany", E:E, "Brown Trout")
Show the answer
4,404
=SUMIFS(D:D, A:A, "Albany", E:E, "Brown Trout")
2. Find the total number of Brown Trout in Genesee River.
Show the answer
12,524
=SUMIFS(D:D, C:C, "Genesee River", E:E, "Brown Trout")
3. Find the total number of Rainbow Trout in Broome county.
Show the answer
20,785
=SUMIFS(D:D, A:A, "Broome", E:E, "Rainbow Trout")
4. You decide you only like fishing in lakes, so you want to find the total number of Brown Trout in any body of water that ends in the word "Lake." Hint: Body of water should be equal to "*Lake".
Show the answer
113,880
=SUMIFS(D:D, C:C, "*Lake", E:E, "Brown Trout")
5. Find the total number of Brown Trout in any body of water that ends in the word "Lake" in Albany county.
Show the answer
2,418
=SUMIFS(D:D, C:C, "*Lake", E:E, "Brown Trout", A:A, "Albany")
6. Find the total number of Brown Trout in any body of water that ends in the word "Pond". Round to the nearest whole trout.
Show the answer
25,464 (the exact total is 25,464.3)
=SUMIFS(D:D, C:C, "*Pond", E:E, "Brown Trout")
7. Find the total number of Brown Trout in any body of water that ends in the word "Creek" in Broome county. Round to the nearest whole trout.
Show the answer
3,335
=SUMIFS(D:D, C:C, "*Creek", E:E, "Brown Trout", A:A, "Broome")
8. Find the total number of Rainbow Trout in any body of water that ends in the word "Lake" in Clinton county. Round to the nearest whole trout.
Show the answer
6,699
=SUMIFS(D:D, C:C, "*Lake", E:E, "Rainbow Trout", A:A, "Clinton")
9. Genegantslet Creek spans two towns, Greene and Smithville. Find the total number of trout in Genegantslet Creek in Greene.
Show the answer
2,155
=SUMIFS(D:D, C:C, "Genegantslet Creek", B:B, "Greene")
10. Genegantslet Creek spans two towns, Greene and Smithville. Find the total number of trout in Genegantslet Creek in Smithville.
Show the answer
739
=SUMIFS(D:D, C:C, "Genegantslet Creek", B:B, "Smithville")
Workbook: NewYorkTrout.xlsx. Interactive version, marked as you type: Lesson 62: LEN, LEFT, RIGHT, MID Practice.
1. The LEFT function returns a specified number of characters from the left end of a string of text. In the formula bar type in: =LEFT(C4, 5) What is the resulting text?
Show the answer
Catsk
=LEFT(C4, 5)
2. The RIGHT function returns a specified number of characters from the right end of a string of text. In cell G2 try: =RIGHT(C4, 3) What is the resulting text?
Show the answer
eek
=RIGHT(C4, 3)
3. The LEN function returns the length of text in number of characters. Try the following formula: =LEN(A2) How many characters are in the word "Albany"?
Show the answer
6
=LEN(A2)
4. The MID function returns text of a specified length from the middle of a larger string of text, starting at a specified position. The following formula starts on the second character of the text in C2 and returns 6 characters: =MID(C2, 2, 6) What is the resulting text?
Show the answer
asic C
=MID(C2, 2, 6)
5. What does =MID(A2, 2, 4) return?
Show the answer
lban
=MID(A2, 2, 4)
6. What does =MID(E3, 1, 4) return?
Show the answer
Rain
=MID(E3, 1, 4)
7. What does =MID(C4, 4, 7) return?
Show the answer
skill C
=MID(C4, 4, 7)
8. What does =MID(B4, 4, 7) return?
Show the answer
sselaer
=MID(B4, 4, 7)
9. What does =MID(D3, 2, 1) return?
Show the answer
5
=MID(D3, 2, 1)
10. What does =MID(B12, 3, 6) return?
Show the answer
eymans
=MID(B12, 3, 6)
These are lessons 55 to 62 of LogicExcel, a site for learning Excel by doing, in English, German and Spanish. Each answer here was checked against the workbook you download.
Data sources:
- AppleSegmentRevenue.xlsx: Apple Inc. 10-K, segment revenue in $ millions
- WorldCropProduction2018.xlsx: FAOSTAT, Crops and livestock products, production quantity in tonnes, 2018 (bulk release of 2025-12-23). Bananas (item 486), Coffee green (656), Avocados (572). Regional aggregates and the 'China' roll-up are excluded, the latter because it is China, mainland plus China, Taiwan Province of counted twice. A crop a country did not report, or reported as zero, is shown as No Data.
- NewYorkTrout.xlsx: Data.gov - New York State trout stocking