Count cells with text, COUNTIF function and wildcards

by | Feb 3, 2023 | Excel

What to do if we want to count cells with text?

Count cells with text, COUNTIF func­tion and wildcards 

For me, the sim­plest solu­tion is using the COUNTIF func­tion and wild­cards. We have an aster­isk that rep­re­sents any text string, even an emp­ty one. We also have a ques­tion mark that rep­re­sents just one sin­gle sign (Fig. 1). 

Wildcards
Fig. 1 Wildcards

Let’s start with count­ing all cells with text. First, we have to select a prop­er range and press the F4 key to lock it. Then, we have to write an aster­isk in dou­ble quotes so that it counts any text string, even an emp­ty one (Fig. 2). 

=COUNTIF($B$2:$B$11,”*”)

Proper range and an asterisk
Fig. 2 Prop­er range and an asterisk

And just like that we have 4 cells with text. In cell B6 I have past­ed an emp­ty text string and even if we see noth­ing, Excel counts it as a cell with an emp­ty text string. But, what can we do if we don’t want to count emp­ty text strings? We can copy our for­mu­la and add a ques­tion mark next to the aster­isk. It does­n’t mat­ter if it’s before or after the sign (Fig. 4).

=COUNTIF($B$2:$B$11,”?*”)

Formula modification
Fig. 4 For­mu­la modification

This way we count­ed all cells that have at least one sign (Fig. 5). 

Results
Fig. 5 Results

If we want to count all not emp­ty cells, we can change our cri­te­ria to the <> signs (Fig. 6). 

=COUNTIF($B$2:$B$11,”<>”)

Adding <> signs
Fig. 6 Adding <> signs

How­ev­er, the sim­plest solu­tion in most cas­es would be using the COUNTA func­tion, where we don’t have to wor­ry about any cri­te­ria (Fig. 7). 

=COUNTA(B2:B11)

COUNTA function
Fig. 7 COUNTA function

https://www.youtube.com/watch?v=8ywd-H7ALc8

COUNTA Function counting almost everything

by | Oct 27, 2022 | Excel

Today, we are going to talk about the COUNTA func­tion. This func­tion counts almost every­thing, It counts for­mu­la results (Fig. 1),

COUNTA Func­tion count­ing almost everything
Formula results
Fig. 1 For­mu­la results

num­bers writ­ten as text (Fig. 2), 

Numbers written as text
Fig. 2 Num­bers writ­ten as text

dates and time (Fig. 3),

Dates and time
Fig. 3 Dates and time

log­i­cal val­ues (Fig. 4),

Logical values
Fig. 4 Log­i­cal values

num­bers (Fig. 5),

Numbers
Fig. 5 Numbers

and errors (Fig. 6). 

Errors
Fig. 6 Errors

It even counts a sin­gle num­ber or val­ue that we put as an argu­ment to COUNTA func­tion (Fig. 7) 

A number put as an argument
Fig. 7 A num­ber put as an argument

Let’s write an array with four ele­ments. It does­n’t mat­ter how many columns of rows this array has, but it’s impor­tant how many ele­ments there are. So, we have a range of two cells with num­bers, a sin­gle val­ue and four ele­ments (Fig. 8). 

A range, a single value and four elements
Fig. 8 A range, a sin­gle val­ue and four elements

Since COUNTA func­tion counts every ele­ment, it returns 7 (Fig. 9)

The function returned 7
Fig. 9 The func­tion returned 7

How­ev­er, there is one thing that the COUNTA func­tion does­n’t count. It does­n’t cells that are real­ly emp­ty. Cell A14 is real­ly emp­ty, how­ev­er cell B14 isn’t. It has got a for­mu­la that returns an emp­ty text string. COUNTA func­tion counts an emp­ty text string (Fig. 10).

Empty text string
Fig. 10 Emp­ty text string

Even if a for­mu­la returns a val­ue from an emp­ty cell (B15), the COUNTA func­tion will count cell A15 because it has got a for­mu­la (Fig. 11)

A cell with a formula
Fig. 11 A cell with a formula

The for­mu­la in cell B14 was count­ed by the COUNTA func­tion (Fig. 12)

A cell with a formula
Fig. 12 A cell with a formula

How­ev­er, if we delete the for­mu­la, the COUNTA func­tion will return 0 because cells A14 and B14 are now real­ly emp­ty (Fig. 13)

Empty cells
Fig. 13 Emp­ty cells

Sum­ming up, the COUNTA func­tions counts every­thing except for emp­ty cells. 

https://www.youtube.com/watch?v=kFHAkf9zDBg