COUNTIFS: count open orders above a minimum amount

Count the orders that need attention

A print studio wants to review open orders worth at least 100. The question is how many orders need attention, not how much revenue they represent. Enter this five-order list in A1:C6. The amount column contains numbers, even if you later format it as currency.

Pair each range with its condition

COUNTIFS checks B2:B6 for Open and C2:C6 for amounts greater than or equal to 100. A row counts only if both tests pass. The comparison operator belongs inside the quoted criterion.

=COUNTIFS(B2:B6,"Open",C2:C6,">=100")

The answer is three orders

R-401, R-404 and R-405 qualify. The paid order is excluded even though its amount is large enough. R-403 is open, but 80 is below the threshold.

R-404 is the boundary case: its amount is exactly 100. It counts because the formula uses >=100. Replacing that condition with >100 would return 2. Checking the equality boundary is a quick way to catch an off-by-one interpretation in a report.

Move the threshold into a cell

Enter 100 in E2. Join the comparison operator to E2 with an ampersand. The resulting criterion is the text >=100. If E2 changes to 150, the formula counts only R-405 and returns 1.

Do not put the cell reference inside the quotation marks as >=E2. That would make E2 part of the criterion text rather than reading the number stored in the cell.

=COUNTIFS(B2:B6,"Open",C2:C6,">="&E2)

Counting rows is different from counting unique orders

This list has one row per order. If an export instead has one row per line item, an order with three items might be counted three times. Decide what one row represents before using the result as an order count.

A blank status does not match Open. Inconsistent source labels such as Open followed by a trailing space can also cause a row to be missed. Use consistent labels or clean the imported status column before building the report.

Check it by changing one record

Change R-403 from 80 to 100. With the original threshold of 100, the result becomes 4. Then change R-401 from Open to Paid. The count returns to 3. These two changes test the amount rule and the status rule independently.

If you need the value of the qualifying orders, use SUMIFS with the amount column as the sum range. In the original table, the three qualifying orders total 395, but the COUNTIFS answer remains 3.