Level 4 · Criteria strings, wildcards and a threshold cell

So far the criteria were plain values: app, completed, or a cell holding one. A criterion can also hold an operator and wildcards. That turns COUNTIF and SUMIFS into small filters: “at least 250”, “anything but cancelled”, “any coupon at all”.

Operators in a criterion

The criterion is text, so the operator goes inside the quotes with the value:

Criterion	Matches
">=250"	numbers 250 and up
">250"	numbers above 250 (250 itself is out)
"<100"	numbers below 100
"<>cancelled"	every cell that is not cancelled, blanks included
"completed" or "=completed"	exactly completed (any case)
""	empty cells

On this sheet =COUNTIF(E2:E59,">=250") counts the orders of 250 or more: 23 of 58. As in chapter 1, decide what happens at the edge. “Baskets of $250 and up” is >=; “over $250” is >.

Building a criterion from a cell with &

Logan keeps moving the line, so it lives in H1. The tempting formula is wrong:

=COUNTIF(E2:E59,">=H1")

Everything inside quotes is literal text, so this compares each subtotal with the word H1 and returns 0. Instead, keep the operator in quotes and join the cell to it with &:

H4   =COUNTIF(E2:E59,">="&H1)

">="&H1 is evaluated first and becomes the text >=250. Change H1 to 100 and H4 updates. The same trick works for any operator and for dates: "<="&G2, "<>"&B1.

Wildcards

In a text criterion, * stands for any run of characters (including none) and ? for exactly one character:

Criterion	Matches
"BF*"	codes that start with BF, like BFCM25
"*15"	codes that end in 15, like SPRING15 and SUMMER15
"*"	any text at all, so every filled-in cell
"~*"	a literal asterisk (the ~ escapes it)

Blank cells hold no text, so "*" skips them. That makes =COUNTIF(D2:D59,"*") the count of orders that used a coupon: 25. On Sunday every one of them was BFCM25, so =COUNTIF(D2:D59,"BF*") also gives 25. Wildcards only work on text; "2*" will not find numbers that start with 2.

Two “not” tests on one column

“Leave cancelled and refunded out” needs two conditions on the status column. SUMIFS accepts the same range twice:

H5   =SUMIFS(E2:E59,C2:C59,"<>cancelled",C2:C59,"<>refunded")

A row is kept only when it passes both tests, so cancelled rows fail the first and refunded rows fail the second. The result, 17,756.51, equals =SUMIF(C2:C59,"completed",E2:E59) today, because this day has no pending orders. The two formulas differ when a new status appears: the <> version keeps it, the completed version drops it. Pick the one that matches the definition your team uses.

Common mistakes

• Cell reference inside the quotes. ">=H1" is the text H1, not the value in H1.
• Two <> tests joined in one string. "<>cancelled,refunded" looks for that exact text. Use two pairs.
• <> and blank cells. "<>cancelled" also matches empty status cells. On a clean export that’s fine; on a messy one it can sweep in rows you didn’t mean. The next level looks at this.
• Wildcards on numbers. * and ? match text only.