COUNTIFS in Excel: How to Count OR Criteria

Formulas & functions · Updated November 18, 2026

COUNTIFS in Excel uses AND logic: a row is counted only when it meets every condition you supply. To count rows that meet one condition OR another, add separate COUNTIFS results or use an array of alternatives inside COUNTIFS and wrap it in SUM; choose the approach carefully when the same row could match more than one alternative.

What COUNTIFS Does—and What “OR” Means

COUNTIFS counts records that satisfy criteria across one or more ranges. It is useful when a question combines conditions, such as “count orders from the East region with a value of at least 100.” In that example, a qualifying row must meet both requirements, so the logic is AND.

OR logic asks whether a row meets at least one of several alternatives. “Count orders from the East or West region” is an OR question. COUNTIFS does not switch to OR logic just because you give it more criteria: separate criteria pairs are combined as AND. This distinction is the source of many incorrect counts.

In Microsoft 365 for Windows, the function syntax is COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). Each criteria range is paired with a condition, and you can add more pairs. The ranges must have matching dimensions. The function is also available in Excel for Mac and Excel for the web, and the formulas below work in those versions too.

Count Several Alternatives in One Range

For alternatives in the same column, one concise method is to give COUNTIFS an array of criteria and sum the returned counts. Assume regions are in A2:A7, sales values are in B2:B7, and statuses are in C2:C7. The six records are East/120/Open, West/80/Open, North/150/Pending, East/90/Closed, West/130/Pending, and East/110/Pending. To count East or West:

=SUM(COUNTIFS(A2:A7,{"East","West"}))

The result is 5: three records are East and two are West. The array constant supplies two alternatives; COUNTIFS calculates a count for each, and SUM combines those counts. In Excel’s standard English formula syntax, commas separate the array items. If your Excel installation uses different regional list separators, it may display or require different separators.

This pattern also works when there is an additional condition that every alternative must satisfy. To count records in East or West where sales are at least 100, with the same sample layout:

=SUM(COUNTIFS(A2:A7,{"East","West"},B2:B7,">=100"))

The result is 3: the qualifying rows are East/120, West/130, and East/110. The region is one of the two listed alternatives, while the sales threshold applies to both. SUM is important because it combines the separate counts produced for East and West.

Use Separate COUNTIFS Calls for Clarity

You can express the same simple OR test by adding COUNTIFS calls. This is often easiest to read when there are only two or three alternatives, or when the alternatives have different conditions. Using the same region data in A2:A7, count East or West like this:

=COUNTIFS(A2:A7,"East")+COUNTIFS(A2:A7,"West")

This returns 5. Each COUNTIFS counts one region, and the plus sign adds the results. The approach is explicit and easy to extend, but a long formula with many alternatives can become cumbersome. If an alternative is added later, it must be added to the formula as another term.

For a condition that includes a comparison, put the comparison operator inside the criteria string. With sales in B2:B7, the following counts values of at least 100:

=COUNTIFS(B2:B7,">=100")

To count sales that are at least 100 or below 50, use two separate terms:

=COUNTIFS(B2:B7,">=100")+COUNTIFS(B2:B7,"<50")

The sample returns 4: three sales are at least 100 and one is below 50. The ranges are mutually exclusive here, so no record is counted twice.

Combine OR Alternatives with Other Conditions

OR logic can be combined with AND logic. For example, you might want East orders with either an Open or Pending status. The region must be East, and the status may be either of two choices. With regions in A2:A7 and statuses in C2:C7:

=SUM(COUNTIFS(A2:A7,"East",C2:C7,{"Open","Pending"}))

The result is 2: East/Open and East/Pending each appear once in the sample. This structure is useful for a fixed set of statuses, categories, or names. It says: count the rows matching East and Open, plus the rows matching East and Pending.

Be more cautious when the OR alternatives can overlap. Suppose the question is “count records where the region is East or sales are at least 100.” A row that is East and also has sales of at least 100 satisfies both tests. Simply adding the two counts would count that row twice.

With region in A2:A7 and sales in B2:B7, subtract the overlap to count each record once:

=COUNTIF(A2:A7,"East")+COUNTIF(B2:B7,">=100")-COUNTIFS(A2:A7,"East",B2:B7,">=100")

The answer is 5. Three records are East, four have sales of at least 100, and two satisfy both conditions; therefore, 3 + 4 − 2 = 5 unique records. This is the inclusion-exclusion method. It is useful when the alternatives describe different columns and a record might meet more than one alternative.

Criteria for Text, Wildcards, and Dates

COUNTIFS criteria can be exact text, numbers, comparisons, cell references, or wildcard patterns. For example, an exact text criterion such as "Open" matches that text. The asterisk wildcard represents any number of characters, while the question mark represents one character. To count text beginning with “East,” with region names in A2:A100, use:

=COUNTIFS(A2:A100,"East*")

A tilde escapes a wildcard if you need to search for the literal character. For instance, use "~*" as a criterion to match a literal asterisk. Wildcards apply to text matching; they are not substitutes for numerical comparison operators.

For date ranges, use a start date inclusive and an end date exclusive. Assume dates are in D2:D100, the start date is in F1, and the day after the desired final date is in G1. Then:

=COUNTIFS(D2:D100,">="&F1,D2:D100,"<"&G1)

This counts dates from the start of F1 up to, but not including, G1. Using an exclusive upper boundary is especially helpful if the cells contain date-and-time values: a criterion of less than the next day includes every time on the final date. Make sure F1 and G1 contain real Excel dates, not text that only looks like dates.

Common COUNTIFS OR Mistakes

  • Expecting criteria pairs to mean OR. Separate pairs in COUNTIFS are AND conditions. Put alternative counts together with SUM or addition when OR is intended.
  • Adding overlapping counts without adjustment. If a record meets two alternatives, simple addition counts it twice. Use inclusion-exclusion, or another method that counts unique rows.
  • Using ranges of different sizes. Keep all criteria ranges aligned and the same height and width, such as A2:A100 and B2:B100. Mismatched ranges can return an error or make the calculation unsuitable.
  • Forgetting that blanks and empty strings are different in some data. Imported data may contain formulas returning an empty string, spaces, or genuinely empty cells. A count can be surprising if values that look blank are not stored the same way.
  • Putting a comparison operator outside the criteria text. Criteria such as greater than 100 should be written as ">100"; when the threshold is in a cell, join the operator and reference, as in ">"&F1.
  • Assuming displayed values are stored values. A cell formatted to show 100 may contain 99.6. COUNTIFS evaluates the underlying value, not merely the rounded display.

When to Use COUNTIF, SUMPRODUCT, or a Helper Column

COUNTIF is the simpler choice when there is only one range and one criterion. For OR across alternatives in that same range, either add COUNTIF calls or use the array-and-SUM pattern. COUNTIFS is preferable when each qualifying record must also meet other conditions, such as a region plus a date range.

For more complicated row-by-row logic, SUMPRODUCT can express combinations that are awkward to write as repeated COUNTIFS calls. It is powerful, but its expressions are less immediately readable and can be more difficult to maintain. For a workbook shared with colleagues, a helper column that labels each row as qualifying or not may be clearer, particularly when the OR rule is long or changes frequently. The helper column also makes it easier to inspect which records were included.

Choose based on the rule, not just on formula length. If alternatives are mutually exclusive and simple, summed COUNTIFS calls are straightforward. If alternatives overlap, explicitly prevent double-counting. If the rule is complex enough that someone cannot easily verify it, a helper column or a carefully documented formula may be the safer design.

Frequently Asked Questions

Does COUNTIFS use AND or OR?
COUNTIFS uses AND across its criteria pairs: a row must satisfy every supplied pair. To get OR logic, combine counts for the alternatives, or use an array of alternatives inside COUNTIFS and sum the results.

How do I count cells that match either of two words?
Use a SUM around COUNTIFS with an array of the two text criteria, or add one COUNTIFS call for each word. If both criteria could match the same cell or record, check whether your method counts it more than once.

Can COUNTIFS handle OR across different columns?
Yes, but consider overlap. If a row can satisfy both conditions, adding the two counts overcounts it. Subtract the count of rows meeting both conditions, or use a row-level method that evaluates whether either condition is true.

Why does my COUNTIFS formula return zero?
Check for mismatched text, extra spaces, criteria written with the wrong comparison syntax, and numbers or dates stored as text. Also verify that all criteria ranges cover the intended rows and have matching dimensions.

Can I use cell references for OR criteria?
Yes. You can use separate COUNTIFS calls whose criteria are cell references, or build an array of alternatives where appropriate. If you concatenate a comparison operator with a cell reference, join them with the ampersand operator.

Is COUNTIFS available in Excel for Mac and the web?
Yes. COUNTIFS is available in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Formula separators can vary with regional settings, but the counting logic is the same.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.