Course outline / Logical functions
Lesson 19 of 75 · Intermediate · 4 exercises
Criteria from cells
Let COUNTIF and SUMIF read their conditions from cells, so a report updates when you change one value.
What you'll practise
- Use a cell as the criteria
- Join an operator to a cell
- Total for the chosen region
- Two conditions from cells
Use a cell as the criteria
Instead of typing "East" into a formula, point at a cell that holds it. Change the cell, and the answer changes too.
=COUNTIF(B2:B6, G2)
In cell F2, count the orders in B2:B6 for the region typed in G2.
Join an operator to a cell
To compare against a cell, put the operator in quotes and join it to the cell with &:
=COUNTIF(D2:D6, ">"&H2) counts amounts greater than whatever is in H2.
Writing ">H2" would look for the text H2, which is a common mistake.
In cell F2, count the amounts in D2:D6 greater than the value in H2.
Total for the chosen region
In cell F2, add up the amounts in D2:D6 for the region in G2.
Two conditions from cells
In cell F2, add up the amounts in D2:D6 where the region matches G2 and the amount is greater than H2.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.