Confident Cells

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.

Your turn

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.

ABCDEFGH
1EmployeeTeamSalesTeamSales
2Grace JohnsonFinance39Employees
3Ollie SmithSales40
4Ivan ShahIT85
5Sam WilsonFinance79
6Tanya HallOperations61
7Dev HallIT43
8Owen MensahIT84
9Liam SmithIT57
10Kerry BakerSales45
11Jess MensahFinance34
12Chen EvansIT51
13Zoe KhanOperations10
14Lucy ClarkeIT81
15Alex MensahFinance34
16Marek WrightIT43
17Maria GreenOperations20
18Hamza JohnsonSales16
19Tom GreenSales70
20Ella ShahFinance54
21Priya AliFinance51

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

ABCDEF
1NameTeamSalesTeamSales
2AmiraSales52How many
3BenFinance30Target40
4CaraSales38At target
5DevSupport45
6EwaSales40
  1. =COUNTIF(B2:B6,F1 in . 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.
  2. =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

ABCDEF
1NameTeamSalesTeamSales
2AmiraSales52How many
3BenFinance30Target40
4CaraSales38At target
5DevSupport45
6EwaSales40
  1. =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.
  2. =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: ">="&H1 and "<="&H2.

What you'll practise

  1. 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.
  2. 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.
  3. In H2, count the employees whose name contains the letters in H1 ("es" at the moment) anywhere, not just at the start.
  4. In H1, count the orders that aren't cancelled and have a delivery date booked in column C. Pending orders count too.
  5. In H3, total the sales made from the date in H1 to the date in H2, including both of those days.