The COUNTX function supports the “Count cells that contain”-type searches and helps you to write short, easy-to-understand formulas.
You can use various formulas and workarounds with the built-in Excel functions to count cells containing text, numbers, or a boolean value. This guide will demonstrate how to create formulas using the COUNTX function.
Syntax
=COUNTX(range, type)Arguments
The COUNTX function uses two required arguments.
- range: the range of cells you want to search for a specified value type.
- type: an integer between 1 and 7 controls the search type.
Examples
In the example, our data set is in the range B3:B10. So, here is the list of formulas you can use with COUNTX. But first, let us look at the second argument; it provides solutions for many formula challenges in Excel.
Generic formula:
=COUNTX(range, “search_type”)Depending on your task, set the second argument to an integer between 1 and 7.
- 1: count text values.
- 2: count numeric values.
- 3: count dates.
- 4: count Boolean values
- 5: count cells that contain errors.
- 6: count cells that contain formulas.
- 7: count cells that do not contain errors.
For example, if you want to count the number of cells that contain Boolean values (TRUE or FALSE), use the following formula:
=COUNTX(B3:B10, 4)Another example is to count cells that contain date values:
=COUNTX(B3:B10, 3)COUNTX alternatives
Excel has many built-in functions if you are unfamiliar with the COUNTX function. However, it is usually worth using the SUMPRODUCT function and creating a nested formula. In addition, it may come with some issues and typos.
If you want to count something, do not forget: SUMPRODUCT needs an array-type input, which will count the cells that meet the given criteria. Good to know that you have to convert TRUE or FALSE values (Boolean values) to 1s and 0s to get the result.
Related formulas:
