Skip to content
en
Practical GuidesAll tools

Categories

Developer ToolsEncoding & DecodingJSON & DataExcel ToolsHash & ChecksumText ToolsDate & TimeNetwork ToolsFile ToolsMarkdown & Diagrams

Excel COUNTIFS Formula Generator

Describe up to four conditions and get a COUNTIFS that counts the rows matching all of them, with dates and wildcards written correctly.

Processed locally in your browser

Options

A cell (B2), a number, text (quoted for you), a date like 2024-01-31, or a formula starting with "=".

Leave the range empty to skip this row.

Leave the range empty to skip this row.

Leave the range empty to skip this row.

Excel uses ";" when the regional settings use a decimal comma. Decimal numbers are then written with a comma too.

What is Excel COUNTIFS Formula Generator?

COUNTIFS counts the rows where every condition holds, for example the invoices marked "Paid" that were issued in 2024. Each condition is a range plus a criterion, and the criterion syntax is what trips people up: operators sit inside quotes, comparisons with a cell use the & operator, and dates should be written with DATE() so that regional formats do not matter.

This generator asks for up to four rows of range, condition and value. Its default shows a classic status-plus-date-range count. It writes the criteria for equals, not equal, greater or less than, contains, does not contain, begins with, ends with, is blank and is not blank, escapes the wildcard characters ? * and ~ when you mean them literally, and warns about ranges of different heights. With a single condition it also shows the shorter COUNTIF, and a SUMPRODUCT form is given for old Excel.

How does it work?

  1. For the first condition enter the range to test (for example B2:B100), choose the condition and type the value, such as Paid.
  2. Add more rows to narrow the count: a second and third row on the same date column give a date range, for example from 2024-01-01 to 2024-12-31.
  3. Leave unused rows without a range to skip them, and pick the separator and function language of your Excel.
  4. Copy the COUNTIFS, or the COUNTIF or SUMPRODUCT alternative, and check the explanation and warnings.

Common use cases

  • Counting orders with a given status placed within a date range.
  • Counting how many rows have a blank cell in a required column, or how many are filled.
  • Counting entries whose name begins with a code or contains a keyword using wildcard criteria.
  • Counting values inside a numeric band, such as scores at least 50 and below 80.

Examples

Describe up to four conditions and get a COUNTIFS that counts the rows matching all of them, with dates and wildcards written correctly.

Privacy

Excel COUNTIFS Formula Generator runs entirely in your browser. The text or files you provide are processed on your device and are not uploaded, logged or stored on our servers.

Limitations

All conditions must be true on the same row (AND); counting rows that match any of several values needs several COUNTIFS added together. The generator does not read your workbook, and all ranges must have the same size.

Frequently asked questions

How do I count between two dates or two numbers?

Use two rows on the same range: greater than or equal to the lower limit and less than or equal to the upper limit. For dates the tool writes ">="&DATE(2024,1,1) and "<="&DATE(2024,12,31), which work whatever the computer's date format. Use "less than" instead of "less than or equal" for an upper limit that is excluded.

How do I count blank or non-blank cells?

Choose "is blank" or "is not blank" for the row. The criterion becomes "" for cells that are empty or hold empty text, and "<>" for cells that have any content. A cell containing only a space counts as not blank.

Is COUNTIFS case-sensitive, and can it match partial text?

It ignores upper and lower case. For partial text use "contains", "begins with" or "ends with": they write the wildcards * for you and escape any ? * or ~ that you typed so they match literally.

Related tools

More tools in Excel Tools →