COUNTIFS with Multiple Criteria in the Same Column (OR Logic)

What you will build

Brightline Trading Co. tracks 20 client tasks. Each task has an owner, a status (Not Started, In Progress, On Hold, Done or Cancelled), a priority and an hours estimate. The question: how many tasks are still open, meaning Not Started or In Progress? Both values live in the same Status column.

Step 1: Look at the data and the question

1. Put the task list in A3:F23 with the headers Task, Client, Owner, Status, Priority and Hours, one task per row.
2. In H2:I2 add the headers Question and Count. Each row below asks one question in column H and answers it in column I.
3. Make sure the statuses are spelled the same way every time. A drop-down list (Data > Data Validation > List) is the easiest way.

Step 2: Why two criteria on the same column return 0

The first thing most people try is in I3:

=COUNTIFS($D$4:$D$23,"Not Started",$D$4:$D$23,"In Progress")

COUNTIFS counts a row only when every condition is true. This formula asks for rows where the status is Not Started and In Progress at the same time. A cell holds one value, so no row qualifies.

Step 3: OR in one column with an array constant

Give COUNTIFS both statuses at once, in curly braces, and wrap it in SUM. In I4:

=SUM(COUNTIFS($D$4:$D$23,{"Not Started","In Progress"}))

• {"Not Started","In Progress"} is an array constant: a list typed straight into the formula.
• COUNTIFS runs once for each item and returns two counts, {5,6}.
• SUM adds them: 11. Type it normally; array constants do not need Ctrl+Shift+Enter in any version.

For two or three values, writing the COUNTIFS out and adding them gives the same answer and is easy to read. In I5:

=COUNTIFS($D$4:$D$23,"Not Started")+COUNTIFS($D$4:$D$23,"In Progress")

Step 4: Combine OR with AND on other columns

Everything after the array is a normal AND condition. To count open tasks that are also High priority, in I6:

=SUM(COUNTIFS($D$4:$D$23,{"Not Started","In Progress"},$E$4:$E$23,"High"))

That returns 5: T-102, T-107, T-110, T-115 and T-119. You can also use a second OR list on a different column. For open tasks owned by Ana or Ben, in I7:

=SUM(COUNTIFS($D$4:$D$23,{"Not Started","In Progress"},$C$4:$C$23,{"Ana";"Ben"}))

The first list uses a comma (it runs across) and the second uses a semicolon (it runs down). Excel pairs every status with every owner, so it counts all four combinations and SUM adds them: 8. If both lists used commas, Excel would pair them item by item (Not Started with Ana, In Progress with Ben) and miss the other two combinations.

Step 5: Keep the criteria in cells, and count number ranges

Hard-coded lists are fine for a quick answer, but a report is easier to change when the criteria live in cells. Type Not Started in H16 and In Progress in H17, then in I8:

=SUMPRODUCT(COUNTIFS($D$4:$D$23,H16:H17))

SUMPRODUCT does the same job as SUM here, but it handles a cell range without Ctrl+Shift+Enter in Excel 2019 and earlier. In Microsoft 365, =SUM(COUNTIFS($D$4:$D$23,H16:H17)) also works.

Two criteria on the same column are not always wrong. For a range of numbers, AND is exactly what you want. Tasks estimated at 10 to 20 hours, in I9:

=COUNTIFS($F$4:$F$23,">=10",$F$4:$F$23,"<=20")

The same ideas carry over. =SUM(SUMIFS($F$4:$F$23,$D$4:$D$23,{"Not Started","In Progress"})) in I11 adds the hours on open tasks: 148. And exclusions on the same column do work as AND: =COUNTIFS($D$4:$D$23,"<>Done",$D$4:$D$23,"<>Cancelled") in I12 returns 13, the 11 open tasks plus the 2 on hold.

Step 6: Verify the count with SUMPRODUCT

A second formula that works in a different way is the best check. In I10:

=SUMPRODUCT(--((($D$4:$D$23="Not Started")+($D$4:$D$23="In Progress"))>0))

Each comparison returns TRUE or FALSE for every row. Adding them gives 1 for a row that matches either status and 0 otherwise; >0 and -- turn that into a 1 or 0 per row, and SUMPRODUCT adds them up. In I13, =IF(AND(I4=I5,I4=I10),"Ties","Check") confirms the three methods agree.

Where you will use this

Status counts are the backbone of any tracker. In a project tracker with status and due dates, this formula counts open work; in a sales pipeline tracker, it counts deals in any of several stages. Combine it with a date condition to count overdue items with COUNTIFS, or switch to SUMIFS to total amounts the same way, as in SUMIFS between two dates. In an accounts receivable aging report, the same pattern counts invoices in the 61-90 or 90+ buckets together. To list the matching rows instead of counting them, use the FILTER function.

Common mistakes and quick fixes

Frequently asked questions

Final checklist

• Two exact values on the same column use an array constant and SUM, not two criteria pairs.
• Extra AND conditions are added as normal range/criteria pairs.
• A second OR list on another column uses a semicolon.
• Criteria in cells use SUMPRODUCT for older Excel versions.
• OR across different columns uses SUMPRODUCT so rows are not double-counted.
• A SUMPRODUCT check agrees with the COUNTIFS result.

Once OR logic clicks, the same pattern works in SUMIFS and AVERAGEIFS. SUMIFS between two dates and gross margin vs markup are good next steps.