Available in:Excel 2007 or later (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Statistical

COUNTIFS

AANTALLEN.ALS in Dutch Excel

Counts the rows in which every condition you set is met, each condition tested on its own range.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: F3 holds the formula
Row	A	B	C	E	F
1	Entry no.	Journal	Account	Journal	MEM
2	25-0401	VRK	80008000	Account	80008000
3	25-0402	MEM	80008000	Lines	33=COUNTIFS(B2:B8, F1, C2:C8, F2)=COUNTIFS(B2:B8; F1; C2:C8; F2)
4	25-0403	MEM	40004000
5	25-0404	INK	45004500
6	25-0405	MEM	80008000
7	25-0406	BNK	11001100
8	25-0407	MEM	80008000

Result: 3

=COUNTIFS(B2:B8, F1, C2:C8, F2)

=COUNTIFS(B2:B8; F1; C2:C8; F2)

What COUNTIFS does and when to use it

What it does: it counts the rows that meet all your criteria, each tested on its own range, for example the manual postings on one revenue account.

When to use it: for any count with two or more conditions, and for a range between two limits (two conditions on one column). It replaces COUNTIF when one condition is not enough, and most SUMPRODUCT counts.

Two main gains:

Several conditions at once: up to 127 pairs of range and criterion in one formula, instead of a SUMPRODUCT with multiplied tests.
A between test in one formula: use the same range twice, once with ">=" and once with "<".

How Excel evaluates it

Excel first checks that every criteria range has the same number of rows and columns; if not, it stops with #VALUE!.

It then goes through the positions one by one. At each position it tests the cell in criteria_range1 against criteria1, the cell in criteria_range2 against criteria2, and so on. A position adds 1 to the count only if every test passes; one failure is enough to skip it. The result is the number of positions that passed all tests.

Watch out: The conditions are always joined with AND. For 'store A or store B' you cannot list both stores in one COUNTIFS (the result is 0); add two COUNTIFS together.

Mental model

A checklist per row. A row is counted only when every box on its checklist is ticked; one empty box and it is left out.

Terms used on this page: criteria, range, cell

Syntax

Official COUNTIFS documentation

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

COUNTIFS(criteria_range1; criteria1; [criteria_range2; criteria2]; ...)

Arguments
Argument	Type	Meaning
`criteria_range1`	Required	The range a condition is tested against.
`criteria1`	Required	The condition, such as ">100", "IT" or a cell reference.
`criteria_range2`	Optional	Range for a further condition; same size as the first range.
`criteria2`	Optional	The test for criteria_range2, written like criteria1.

Return value:A whole number: how many positions pass every criterion

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 18 KB)

Beginner

Manual postings on one revenue account

You are testing journal entries in the XAF auditfile of Veldhuis Installatie BV. Manual entries in the memorial journal (MEM) on the revenue account 8000 carry a higher risk of management override. You want to know how many lines meet both conditions: journal MEM and account 8000.

Sheet excerpt: F3 holds the formula
Row	A	B	C	E	F
1	Entry no.	Journal	Account	Journal	MEM
2	25-0401	VRK	80008000	Account	80008000
3	25-0402	MEM	80008000	Lines	33=COUNTIFS(B2:B8, F1, C2:C8, F2)=COUNTIFS(B2:B8; F1; C2:C8; F2)
4	25-0403	MEM	40004000
5	25-0404	INK	45004500
6	25-0405	MEM	80008000
7	25-0406	BNK	11001100
8	25-0407	MEM	80008000
9

Formula cell (F3)Input

Formula in F3

=COUNTIFS(B2:B8, F1, C2:C8, F2)

=COUNTIFS(B2:B8; F1; C2:C8; F2)

Result: 33

Step by step

Excel reads the two criteria: F1 is MEM and F2 is 8000. B2:B8 and C2:C8 both have 7 rows, so the sizes match.
First pair, journal = MEM: rows 3, 4, 6 and 8 pass; rows 2, 5 and 7 (VRK, INK, BNK) fail.
Second pair, account = 8000, for the rows still in: row 3 yes, row 4 no (4000), row 6 yes, row 8 yes.
Rows 3, 6 and 8 pass both tests: entries 25-0402, 25-0405 and 25-0407. COUNTIFS returns 3.
Change F2 to 4000 and only 25-0403 passes both tests: the result becomes 1.

Watch out: Entry 25-0401 is on account 8000 and entry 25-0403 is a MEM entry, but neither counts, because each passes only one of the two tests. If you expected 5, you were counting with OR in your head.

Purchase invoices per amount band without double counting

For a sample of purchase invoices you group the amounts into bands: under 500 (below the posting threshold you do not follow up), 500 to under 5,0005.000, and 5,0005.000 to under 25,00025.000 (up to performance materiality). Two invoices sit exactly on a band edge, 500 and 5,0005.000. The band counts must add up to the number of invoices, so every edge belongs to one band only.

Sheet excerpt: F3 holds the formula
Row	A	B	D	E	F
1	Invoice no.	Amount	From	Below	Invoices
2	F-2501	€425.00€ 425,00	00	500500
3	F-2502	€5,000.00€ 5.000,00	500500	50005000	33=COUNTIFS($B$2:$B$9, ">=" & D3, $B$2:$B$9, "<" & E3)=COUNTIFS($B$2:$B$9; ">=" & D3; $B$2:$B$9; "<" & E3)
4	F-2503	€1,400.00€ 1.400,00	50005000	2500025000
5	F-2504	€495.00€ 495,00
6	F-2505	€18,250.00€ 18.250,00
7	F-2506	€500.00€ 500,00
8	F-2507	€12,400.00€ 12.400,00
9	F-2508	€2,300.00€ 2.300,00
10

Formula cell (F3)InputNote: The formula is in F2 and copied down to F4. The walkthrough follows the copy in F3.

Formula in F3

=COUNTIFS($B$2:$B$9, ">=" & D3, $B$2:$B$9, "<" & E3)

=COUNTIFS($B$2:$B$9; ">=" & D3; $B$2:$B$9; "<" & E3)

Result: 33

Step by step

Excel builds the two criteria from the band in row 3: ">=" & D3 becomes ">=500" and "<" & E3 becomes "<5000".
First pair, at least 500: 425 no, 5000 yes, 1400 yes, 495 no, 18250 yes, 500 yes, 12400 yes, 2300 yes.
Second pair, below 5000, on the same range: of the six that passed, 1400, 500 and 2300 are below 5000; 5000, 18250 and 12400 are not. The invoice of exactly 5000 drops out here.
Three invoices pass both tests, so F3 shows 3. The invoice of exactly 500 counts in this band because the lower limit uses >=.
Without the second pair, COUNTIFS would count everything from 500 up: 6. The second condition on the same column is what closes the band.
The other copies give F2 = 2 (425 and 495) and F4 = 3 (5000, 18250 and 12400). 2 + 3 + 3 = 8, exactly the number of invoices.

Watch out: With <= at the top as well, both edge invoices count twice: 500 in F2 and F3, 5000 in F3 and F4. The three counts become 3, 4 and 3 and add up to 10 for 8 invoices.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

A between test is two conditions on one range, joined by AND. Using >= at the bottom and < at the top makes the bands touch without overlapping, so each edge value lands in exactly one band. The $ signs keep the invoice range fixed while the band limits change per row.

More to watch out for

An invoice of 25,00025.000 or more falls outside every band. Add a last band with a very high upper limit, or check the total against COUNT(B2:B9).

Variations

=COUNTIFS($B$2:$B$9, ">=" & D3)

=COUNTIFS($B$2:$B$9; ">=" & D3)

One pair only: an 'at least' count, useful when the bands are cumulative.

Advanced

December postings in two journals for the cut-off test

For the cut-off test at year end 31 December 2025 you want one figure: the lines posted in December 2025 in the sales journal (VRK) and the memorial journal (MEM) together. The auditfile also has lines from November, January and other journals. COUNTIFS joins conditions with AND, so 'VRK or MEM' needs a different construction.

Sheet excerpt: F4 holds the formula
Row	A	B	C	E	F
1	Entry no.	Date	Journal	Month	1 Dec 2025
2	25-1101	27 Nov 2025	VRK	Journal 1	VRK
3	25-1202	3 Dec 2025	MEM	Journal 2	MEM
4	25-1203	11 Dec 2025	VRK	Lines	44=SUM(COUNTIFS(B2:B9, ">=" & F1, B2:B9, "<=" & EOMONTH(F1, 0), C2:C9, F2:F3))=SUM(COUNTIFS(B2:B9; ">=" & F1; B2:B9; "<=" & EOMONTH(F1; 0); C2:C9; F2:F3))
5	25-1204	17 Dec 2025	BNK
6	25-1205	24 Dec 2025	MEM
7	25-1206	31 Dec 2025	VRK
8	26-0001	2 Jan 2026	MEM
9	25-1207	19 Dec 2025	INK
10

Formula cell (F4)Input

Formula in F4

=SUM(COUNTIFS(B2:B9, ">=" & F1, B2:B9, "<=" & EOMONTH(F1, 0), C2:C9, F2:F3))

=SUM(COUNTIFS(B2:B9; ">=" & F1; B2:B9; "<=" & EOMONTH(F1; 0); C2:C9; F2:F3))

Result: 44

Step by step

Excel evaluates the innermost function first. EOMONTH(F1, 0) returns the last day of the month in F1: 31 December 2025, serial number 46022. F1 itself is 1 December 2025, serial 45992.
The date criteria become ">=45992" and "<=46022". 25-1101 (Nov 27) and 26-0001 (Jan 2) fail them; the other six rows are in December.
The journal criterion is the range F2:F3, two values, so COUNTIFS runs twice and returns two counts. For VRK: 25-1203 and 25-1206 pass (December 31 is included by <=), giving 2.
For MEM: 25-1202 and 25-1205 pass; 26-0001 is a MEM line but falls in January. That gives 2. COUNTIFS returns {2; 2}.
SUM adds the two counts: 2 + 2 = 4.
Change F1 to 1 January 2026 and the window becomes January 1 to January 31. Only 26-0001 qualifies, and the result is 0 + 1 = 1.

Watch out: Silently wrong: writing the journals as two pairs, C2:C9, F2, C2:C9, F3, returns 0 without an error, because no line belongs to VRK and MEM at once.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

Inside one COUNTIFS every condition is AND. Giving one criterion two values makes COUNTIFS calculate once per value, each with the full set of AND conditions, and SUM then adds the results, which is OR between the journals. A line has one journal, so the two journals cannot overlap and nothing is counted twice.

More to watch out for

If the dates carry a time of day, a posting at 15:00 on December 31 is larger than 46022 and drops out. Use "<" & EOMONTH(F1, 0) + 1 to include the whole last day.
In Excel 2019 and older, SUM around a COUNTIFS with a range as criterion must be confirmed as an array formula. SUMPRODUCT instead of SUM works without that.

Variations

=COUNTIFS(B2:B9, ">=" & F1, B2:B9, "<=" & EOMONTH(F1, 0), C2:C9, F2) + COUNTIFS(B2:B9, ">=" & F1, B2:B9, "<=" & EOMONTH(F1, 0), C2:C9, F3)

=COUNTIFS(B2:B9; ">=" & F1; B2:B9; "<=" & EOMONTH(F1; 0); C2:C9; F2) + COUNTIFS(B2:B9; ">=" & F1; B2:B9; "<=" & EOMONTH(F1; 0); C2:C9; F3)

Two COUNTIFS added together: longer, but it works the same way in every version and is simpler to explain.

=SUMPRODUCT((B2:B9 >= F1) * (B2:B9 <= EOMONTH(F1, 0)) * ((C2:C9 = F2) + (C2:C9 = F3)))

=SUMPRODUCT((B2:B9 >= F1) * (B2:B9 <= EOMONTH(F1; 0)) * ((C2:C9 = F2) + (C2:C9 = F3)))

SUMPRODUCT with * for AND and + for OR, for when the logic grows beyond what COUNTIFS can express.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Journal entry population per journal and account in one spilled grid

For journal entry testing you want an overview of the population for financial year 2025: for each journal, how many lines were posted on revenue account 8000, housing costs 4500 and personnel costs 4000. Instead of nine separate formulas, one COUNTIFS fills the whole grid. The year boundaries sit in input cells, and a line posted on 31 December must be included.

Sheet excerpt: G5 holds the formula
Row	A	B	C	D	F	G	H	I
1	Entry no.	Journal	Account	Date	From	1 Jan 2025
2	25-0114	VRK	80008000	14 Jan 2025	To	31 Dec 2025
3	25-0233	INK	45004500	3 Feb 2025
4	25-0310	MEM	40004000	18 Feb 2025	Journal	80008000	45004500	40004000
5	25-0521	VRK	80008000	21 May 2025	VRK	22=LET( journals, F5:F7, accounts, G4:I4, dateFrom, G1, dateTo, G2, COUNTIFS( B2:B9, journals, C2:C9, accounts, D2:D9, ">=" & dateFrom, D2:D9, "<=" & dateTo ) )=LET( journals; F5:F7; accounts; G4:I4; dateFrom; G1; dateTo; G2; COUNTIFS( B2:B9; journals; C2:C9; accounts; D2:D9; ">=" & dateFrom; D2:D9; "<=" & dateTo ) )	00	00
6	25-0987	INK	45004500	12 Sep 2025	INK	00	22	00
7	25-1288	MEM	80008000	31 Dec 2025	MEM	11	00	11
8	26-0002	VRK	80008000	2 Jan 2026
9	24-1190	MEM	40004000	20 Dec 2024
10

Formula cell (G5)InputSpilled result

Formula in G5

=LET(
  journals, F5:F7,
  accounts, G4:I4,
  dateFrom, G1,
  dateTo, G2,
  COUNTIFS(
    B2:B9, journals,
    C2:C9, accounts,
    D2:D9, ">=" & dateFrom,
    D2:D9, "<=" & dateTo
  )
)

=LET(
  journals; F5:F7;
  accounts; G4:I4;
  dateFrom; G1;
  dateTo; G2;
  COUNTIFS(
    B2:B9; journals;
    C2:C9; accounts;
    D2:D9; ">=" & dateFrom;
    D2:D9; "<=" & dateTo
  )
)

Result: 2 | 0 | 0 / 0 | 2 | 0 / 1 | 0 | 12 | 0 | 0 / 0 | 2 | 0 / 1 | 0 | 1

Step by step

LET names the inputs: journals is the vertical list F5:F7 (VRK, INK, MEM), accounts the horizontal list G4:I4 (8000, 4500, 4000), and the window runs from 1 January to 31 December 2025.
The date pairs remove two lines for every cell of the grid: 26-0002 (2 January 2026) is after dateTo and 24-1190 (20 December 2024) is before dateFrom. Six lines remain.
Because journals is 3 tall and accounts is 3 wide, Excel pairs every journal with every account and runs COUNTIFS 9 times, returning a 3 by 3 grid that spills from G5 to I7.
VRK: account 8000 counts 25-0114 and 25-0521 (26-0002 is outside the year); 4500 and 4000 have nothing. Row: 2, 0, 0.
INK: account 4500 counts 25-0233 and 25-0987; the other accounts have nothing. Row: 0, 2, 0.
MEM: account 8000 counts 25-1288, posted on 31 December and included because the test is <=; account 4000 counts 25-0310 but not 24-1190, which is outside the year. Row: 1, 0, 1. The grid adds up to 6, the six lines inside the window.

Watch out: The mirror-image mistake: "<" & dateTo instead of "<=". It looks like a tidy half-open window, but with a whole date in G2 it drops every line posted on 31 December, so the MEM count on account 8000 becomes 0 and the grid adds up to 5.

More on this example: why it works, 2 more pitfalls, 2 variations

Why it works

When criteria are arrays, COUNTIFS calculates once per element, and a vertical array combined with a horizontal one expands into every combination, the same way a multiplication table does. LET names the four inputs so the long argument list reads as an audit rule, and the date limits are typed once.

Do not use this construction when a condition compares two columns of the same row, such as 'posted after the document date', or when the overview has to be sliced in many different ways: a PivotTable is better there, at the cost of needing a manual refresh. For a single count with column-to-column logic, SUMPRODUCT is the alternative.

More to watch out for

If journals and accounts are both vertical, Excel pairs them element by element instead of making a grid, and returns 3 counts that mix one journal with one account each. Keep one list vertical and the other horizontal.
A journal code exported with a trailing space, MEM , is not counted anywhere, and the grid quietly adds up to less than the list. Clean the column with TRIM first.

Variations

=COUNTIFS(B2:B9, UNIQUE(B2:B9), C2:C9, G4:I4, D2:D9, ">=" & G1, D2:D9, "<=" & G2)

=COUNTIFS(B2:B9; UNIQUE(B2:B9); C2:C9; G4:I4; D2:D9; ">=" & G1; D2:D9; "<=" & G2)

UNIQUE builds the journal list from the data, so a new journal appears in the grid without editing F5:F7. Put the formula next to a UNIQUE list so the labels line up.

=SUMPRODUCT((B2:B9 = F5) * (C2:C9 = G4) * (D2:D9 >= G1) * (D2:D9 <= G2))

=SUMPRODUCT((B2:B9 = F5) * (C2:C9 = G4) * (D2:D9 >= G1) * (D2:D9 <= G2))

One cell of the grid with SUMPRODUCT (VRK, account 8000: 2). Slower and longer, but it can also compare two columns of the same row.

Try it yourself

Type a criterion for the journal and pick a second one for the account: a line is counted only when it passes both.

Sheet excerpt: F2 holds the formula
Row	A	B	C	E	F
1	Entry no.	Journal	Account
2	25-0401	VRK	80008000	Lines	33=COUNTIFS(B2:B9, "x", C2:C9, 8000)=COUNTIFS(B2:B9; "x"; C2:C9; 8000)
3	25-0402	MEM	80008000
4	25-0403	MEM	40004000
5	25-0404	INK	45004500
6	25-0405	MEM	80008000
7	25-0406	BNK	11001100
8	25-0407	MEM	80008000
9	25-0408		45004500

Formula cell
Meets the criterion

=COUNTIFS(B2:B9, "MEM", C2:C9, 8000)

=COUNTIFS(B2:B9; "MEM"; C2:C9; 8000)

Result: 33

=COUNTIFS(B2:B9, "MEM", C2:C9, ">=4500")

=COUNTIFS(B2:B9; "MEM"; C2:C9; ">=4500")

Result: 33

=COUNTIFS(B2:B9, "MEM", C2:C9, "<>8000")

=COUNTIFS(B2:B9; "MEM"; C2:C9; "<>8000")

Result: 11

Common mistakes

Common mistakes
Symptom	Cause	Fix
0 for 'store A or store B'	Store = `Utrecht` and store = `Leiden` as two pairs on the same column: no row can be both.	Pass both values as a range and wrap the result in SUM, as in the Advanced example.
#VALUE! with ranges of different length	The criteria ranges have different lengths, such as `B2:B9` and `C2:C10`, and the result is #VALUE!.	Select the same rows in every range, or use whole Table columns.
Band totals add up to more than the list	Bands written as `">=100"` and `"<=250"` next to `">=250"` and `"<=500"` count a value of exactly 250 twice, and the band totals add up to more than the list.	Make one end inclusive and the other exclusive: `">="` for the lower limit, `"<"` for the upper.
Count changes with the regional setting	A date typed into the criterion as text, `">=1-3-2026"`, is read differently in another regional setting and the count changes.	Put the date in a cell and join it: `">="&F1`. The cell holds a serial number that every setting reads the same way.