“Counting certain cells in Excel” sounds like an easy task, but in practice it has so many variations that I can hardly list them all.
But I will still try to approach this question as structurally as possible.
If the answers to all of them were given on one page, it would be inconvenient. Therefore, the answer to each of the questions is contained in separate links within this article.
How a cell is defined (parameters)
Surprisingly, a seemingly simple rectangular Excel cell is quite a complex object. After all, it is:
- Cell formatting
- Cell formula;
- and cell value.
And each of these cell components can also be defined by many other parameters and characteristics. Let us break them down in order and try to define the corresponding tasks for extracting and counting such cells.
Counting cells by their formatting parameters
As an Excel expert, let me explain how you can count cells based on their formatting parameters. A cell can be formatted in many ways, including text attributes, background, and geometric dimensions. Here are the most common formatting parameters:
Text and its attributes:
- Type (bold, italic, underlined…)
- Size (8, 9, 10…)
- Font (Arial, Verdana, Times New Roman, etc.)
- Font color
- Alignment by width and height.
Background (aka fill):
- Solid
- Pattern
- Gradient
Geometric dimensions:
- Width
- Height
If you want to count cells with a specific formatting parameter, such as bold text or a certain background color, you can use Excel’s built-in functions. For example:
To count cells with bold text, use the COUNTIF function with the criteria of “bold”. The formula would be: =COUNTIF(range, “bold”)
To count cells that are highlighted in color, you can use the COUNTIF function with the criteria of the cell’s background color. The formula would be: =COUNTIF(range, cell color)
To count cells of a certain color, you can use the COUNTIF function with the criteria of the font color. The formula would be: =COUNTIF(range, font color)
By using these formulas, you can easily count cells based on their formatting parameters in Excel.
The only problem is – it is not always easy to extract those formatting parameters from cells.
That’s where !SEMTools “Extract” instruments will help!
Counting cells by their formula parameters
Formulas in cells are either present or absent, but formulas in turn can contain a variety of searchable patterns. In Excel 2013 and later, the FORMULATEXT function will be most useful for this purpose. It allows you to show formulas in cells as text and then you can process them as normal text data.
Counting cells based on the presence of a value
The uppermost parameter of a cell value is its presence. And hence the corresponding tasks:
- Count the empty cells;
- Count non-empty cells;
- Count the number of filled cells;
- Count the number of cells with a certain value.
Counting cells by data type
But the presence of a value can also be narrowed down to the presence of a certain value by its type, without looking deep into the value itself. What types are we talking about:
- Count cells with a logical data type;
- Count the number cells;
- Count text cells
Counting by the characteristics of the cell values themselves
Each cell value can have a number of parameters, which in turn depends on the type of data they contain.
From simple to complex:
Logical values
Here it’s simple – there can be only two values in such cells – TRUE and FALSE, hence the tasks
- calculate the number of TRUTHs in Excel;
- calculate FALSE/false values in Excel.
Numerical values
With numbers it is a bit more complicated, in addition to their presence, numbers can be more or less than certain values, they are often summed up, etc. That is why there are more variations of problems with them:
- Count the numbers in the cells and the cells with numbers;
- Count cells with a value greater than zero;
- Calculate the sum of the filtered cells;
- Calculate the sum of certain cells.
Text values
The most varied tasks for working with text cells:
- Count cells that contain specific text
- at the beginning of the cell,
- at the end,
- anywhere,
- that are identical to the text;
- count the words in the text;
- count certain characters in the text.