Hands-On Excel

Hands-On Excel Challenges Criteria in a cell

SUMIF with a condition in a cell

To compare with a cell, keep the operator in quotes and join the cell with &: =SUMIF(C2:C11, ">="&F2). Writing ">=F2" compares with the letters F2 and gives 0.

CoreAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

14 · Criteria in a cell · Core

Use a cell as the condition

In F5, add up the orders worth at least the minimum in F2. In F6, add up the orders from the region in F3. Then change F2 or F3: your totals should follow.

To match a cell, point at it: F3. To compare with a cell, join the operator and the cell with &: ">="&F2. Inside the quotes, F2 is just two letters.

orders.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · Criteria in a cell

Why put the condition in a cell?

=SUMIF(C2:C11, ">=500") answers one question. Put the 500 in its own cell, point the formula at it, and the same formula answers any minimum you type in. It also shows the reader what the total is based on.

Two cases

You wantWrite
Rows that match the cell exactly=SUMIF(B2:B11, F3, C2:C11)
Rows above, below, or not equal to the cell=SUMIF(C2:C11, ">="&F2)

To match exactly, point at the cell with no quotes. For a comparison, the condition has to be one piece of text like ">=500". & joins two pieces of text, so ">="&F2 builds ">=500" out of the operator and whatever is in F2 right now.

Why ">=F2" gives 0

Everything inside quotes is text, so ">=F2" means "at least the text F2". None of the amounts compare that way, so nothing matches and the total is 0. There's no error to warn you, which makes this mistake easy to miss.

It works the same everywhere

COUNTIF, COUNTIFS, SUMIFS, AVERAGEIF and AVERAGEIFS read their conditions the same way:

=COUNTIF(C2:C11, ">"&F2)
=SUMIFS(C2:C11, B2:B11, F3, C2:C11, ">="&F2)
=COUNTIF(B2:B11, "<>"&F3)

If you copy the formula to other cells, lock the condition cell: ">="&$F$2. See absolute references.

Common mistakes

FAQ

How do I use a cell reference in SUMIF criteria?
For an exact match, point at the cell: =SUMIF(B2:B11,F3,C2:C11). For a comparison, join the operator and the cell: =SUMIF(C2:C11,">="&F2).
Why does SUMIF with ">="&A1 work but ">=A1" doesn't?
Inside quotes, A1 is just text. The & joins the operator to the value in A1, so Excel gets a condition like ">=500".
How do I count cells greater than the value in another cell?
Use =COUNTIF(C2:C11,">"&F2). The same pattern works in COUNTIFS, SUMIFS and AVERAGEIF.
Can I use a date in a cell as the SUMIF condition?
Yes: =SUMIF(A2:A11,">="&F2,C2:C11) adds the rows on or after the date in F2, as long as the column holds real dates, not text.

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Lock just the row or the columnMixed references Advanced · 3 min
  6. 06 Percent of a totalPercentages Starter · 3 min
  7. 07 Up or down by how much?Percent change Core · 3 min
  8. 08 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  9. 09 Pass or fail?IF Core · 3 min
  10. 10 Both or either?AND, OR Core · 3 min
  11. 11 Grade bands with nested IFNested IF Core · 3 min
  12. 12 Count the ones that matchCOUNTIF Core · 3 min
  13. 13 Add up the ones that matchSUMIF Core · 3 min
  14. 14 Use a cell as the conditionCriteria in a cell Core · 3 min
  15. 15 Count with two conditionsCOUNTIFS Core · 3 min
  16. 16 Add up with two conditionsSUMIFS Core · 3 min
  17. 17 Look it upVLOOKUP Core · 3 min
  18. 18 Find the right bracketVLOOKUP approximate match Core · 3 min
  19. 19 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  20. 20 Where is it in the list?MATCH Core · 3 min
  21. 21 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  22. 22 Catch the error, not the bugIFERROR Core · 3 min
  23. 23 Split first and last namesLEFT, MID + FIND Core · 3 min
  24. 24 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min
  25. 25 Mistake hunt: hard-coded numbersReferences instead of typed numbers Core · 3 min
  26. 26 Mistake hunt: numbers stored as textNumbers stored as text Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy