Count rows that meet two conditions with COUNTIFS

Sample data. Deals in A:C, the formula in F2 is copied down for each rep.

The formula

List the reps in a column (E here), click the first count cell and type:

=COUNTIFS(A:A,E2,C:C,">=500")

F2 shows 2: Ana has deals of 750 and 520 at 500 or more (her 95 does not count). Copy it down and Raj shows 1, Kim 0.

• A:A,E2 is the first rule: the Rep column must match the name in E2.
• C:C,">=500" is the second rule: the Amount must be 500 or more.

Rules come in pairs, a column and then its condition, and a row is counted only when every rule is true. Conditions with an operator go in double quotes.

One condition: COUNTIF

With a single rule, COUNTIF does the same job:

=COUNTIF(A:A,"Ana")

Ana has 3 deals in total. COUNTIFS with one pair gives the same answer, so you can use COUNTIFS for everything.

Add a third rule

Add another pair for each extra condition:

=COUNTIFS(A:A,"Ana",B:B,"West",C:C,">=500")

Only one row is Ana, West and 500 or more (the 750 deal), so the result is 1.

Take the limit from a cell

Put the number in a cell (H1 = 500) and join it to the operator with &:

=COUNTIFS(A:A,E2,C:C,">="&H1)

Still 2 for Ana, and now you change the limit without touching the formula.

Between two amounts

Use the same column twice, once for each end:

=COUNTIFS(C:C,">=200",C:C,"<=800")

750, 300 and 520 fall between 200 and 800: 3 deals.

This name or that name

COUNTIFS only counts rows where every rule is true. To count Ana's deals or Raj's deals, add two counts together:

=COUNTIF(A:A,"Ana")+COUNTIF(A:A,"Raj")

3 + 2 = 5. COUNTIF and COUNTIFS work in every Excel since 2007 and in Google Sheets.

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.