Criteria from cells, and wildcards: practice exercises
">="&H1, "*"&H1&"*", "<>". 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In H2, count the employees in the team typed in H1 (Sales at the moment). Use H1 in your formula, so changing H1 changes the count.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Team | Sales | Team | Sales | |||
| 2 | Grace Johnson | Finance | 39 | Employees | ||||
| 3 | Ollie Smith | Sales | 40 | |||||
| 4 | Ivan Shah | IT | 85 | |||||
| 5 | Sam Wilson | Finance | 79 | |||||
| 6 | Tanya Hall | Operations | 61 | |||||
| 7 | Dev Hall | IT | 43 | |||||
| 8 | Owen Mensah | IT | 84 | |||||
| 9 | Liam Smith | IT | 57 | |||||
| 10 | Kerry Baker | Sales | 45 | |||||
| 11 | Jess Mensah | Finance | 34 | |||||
| 12 | Chen Evans | IT | 51 | |||||
| 13 | Zoe Khan | Operations | 10 | |||||
| 14 | Lucy Clarke | IT | 81 | |||||
| 15 | Alex Mensah | Finance | 34 | |||||
| 16 | Marek Wright | IT | 43 | |||||
| 17 | Maria Green | Operations | 20 | |||||
| 18 | Hamza Johnson | Sales | 16 | |||||
| 19 | Tom Green | Sales | 70 | |||||
| 20 | Ella Shah | Finance | 54 | |||||
| 21 | Priya Ali | Finance | 51 |
How it works
A criterion can come from a cell. =COUNTIF(B2:B21,H1) counts whatever is typed in H1, so you can change H1 instead of editing the formula.
When there's a comparison, keep the comparison in quotes and join the cell on with &: ">="&H1. Writing ">=H1" doesn't work, because inside quotes H1 is just text.
Wildcards: * stands for any run of characters, so "*"&H1&"*" means "contains whatever is in H1". "<>" on its own means "not empty".
=COUNTIF(B2:B6,F1) =COUNTIF(C2:C6,">="&F3) =COUNTIF(A2:A6,"*"&F1&"*") =COUNTIF(B2:B6,"<>")
Criteria from a cell
So far, criteria have been typed into the formula: "Sales", ">=40". To change what's counted, you'd have to edit the formula.
Put the criteria in a cell instead, and refer to that cell. Then anyone can change what's counted by typing in the cell, and the formula doesn't change.
Worked example: a cell as the criteria
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Sales | Team | Sales | |
| 2 | Amira | Sales | 52 | How many | ||
| 3 | Ben | Finance | 30 | Target | 40 | |
| 4 | Cara | Sales | 38 | At target | ||
| 5 | Dev | Support | 45 | |||
| 6 | Ewa | Sales | 40 |
=COUNTIF(B2:B6,F1in . The range is the Team column, and the criteria is the cell F1, with no quotes. In quotes, "F1" would just be the letters F1.=COUNTIF(B2:B6,F1)in . It counts whatever F1 says. Sales appears 3 times. Type Finance in F1 and it would give 1.
A comparison with a cell
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Sales | Team | Sales | |
| 2 | Amira | Sales | 52 | How many | ||
| 3 | Ben | Finance | 30 | Target | 40 | |
| 4 | Cara | Sales | 38 | At target | ||
| 5 | Dev | Support | 45 | |||
| 6 | Ewa | Sales | 40 |
=COUNTIF(C2:C6,">=F3")in . This looks sensible, but inside quotes F3 is just the letters F3, not the cell. Nothing matches, so it gives 0.=COUNTIF(C2:C6,">="&F3)in . Keep the comparison in quotes and join the cell on with &. Now it means ">=40": Amira, Dev and Ewa, so 3.
Wildcards and "not"
*stands for any run of characters."S*"means "starts with S", and"*"&F1&"*"means "contains whatever is in F1".<>means "not"."<>Cancelled"is anything except Cancelled, and"<>"on its own means "not empty".- For a range of dates or numbers, use two conditions on the same column:
">="&H1and"<="&H2.
What you'll practise
- In H2, count the employees in the team typed in H1 (Sales at the moment). Use H1 in your formula, so changing H1 changes the count.
- The sales target is in H1. In H2, count the people whose sales are at least the target. Use H1, so the count updates if the target changes.
- In H2, count the employees whose name contains the letters in H1 ("es" at the moment) anywhere, not just at the start.
- In H1, count the orders that aren't cancelled and have a delivery date booked in column C. Pending orders count too.
- In H3, total the sales made from the date in H1 to the date in H2, including both of those days.