Excel - Counting cells according to content.

Question

Excel - Counting cells according to content.

Michael H 0

I have a row of cells, each of which can be blank or contain 1 to 4 different valid characters and possibly any other invalid characters etc. I can count the cells which have only valid characters or only invalid characters, using COUNTIFS. How do I count cells which contain a combination of valid and invalid characters? e.g. Valid - "abcd", "a", "dca", "a b","aaabdcccdd". Invalid - "x", "xyz", "w1123345", $%^&". Mixed valid and invalid (to be counted) - "az", "d123xyz", etc.

Ashish Mathur 101.8K Reputation points Volunteer Moderator

2026-05-03T23:10:02.3766667+00:00

Hi,

Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.
Dana D 5 Reputation points

2026-05-04T01:32:07.4766667+00:00

How do I count cells which contain...

[removed: counting cells was not what was needed.]
Michael H 0 Reputation points

2026-05-04T02:42:12.6633333+00:00

Not accepted. The array I want to process is currently 30 columns and set to get wider, by at least 200 rows. Helper columns will make the worksheet messy, even when hiding those columns. I need a more elegant solution.
Michael H 0 Reputation points

2026-05-04T03:06:52.61+00:00

This is what I am trying to achieve:

For each day of the month(Row). each leader(1,2,3,4) declares availability for each part of the day; Morning, Afternoon, Evening, Night. If not available for a specific period the associated letter is not used. If not available for the whole 24 hours, the cell is blank. Valid letters are M, A, E, N (or lower case). If only invalid characters are used in a cell, it is counted as "Not Valid". Valid characters in a cell with invalid characters are counted correctly with no difficulty. However, it is difficult to count those cells which contain a mix of valid and invalid characters.

For "Not Valid"=COUNTIFS(E4:H4,"<>M",E**A*",E4:H4,"<>E",E4:H4,"<>N")-T4*

T4 = cell under Blank

For "Morning" etc: =COUNTIF(E4:H4,"M")

4 answers

Your answer

Ashish Mathur 101.8K Reputation points Volunteer Moderator

2026-05-03T23:10:02.3766667+00:00

Hi,

Share data in a format that can be pasted in an MS Excel file. Show the expected result very clearly.
Dana D 5 Reputation points

2026-05-04T01:32:07.4766667+00:00

How do I count cells which contain...

[removed: counting cells was not what was needed.]
Michael H 0 Reputation points

2026-05-04T02:42:12.6633333+00:00

Not accepted. The array I want to process is currently 30 columns and set to get wider, by at least 200 rows. Helper columns will make the worksheet messy, even when hiding those columns. I need a more elegant solution.
Michael H 0 Reputation points

2026-05-04T03:06:52.61+00:00

This is what I am trying to achieve:

For each day of the month(Row). each leader(1,2,3,4) declares availability for each part of the day; Morning, Afternoon, Evening, Night. If not available for a specific period the associated letter is not used. If not available for the whole 24 hours, the cell is blank. Valid letters are M, A, E, N (or lower case). If only invalid characters are used in a cell, it is counted as "Not Valid". Valid characters in a cell with invalid characters are counted correctly with no difficulty. However, it is difficult to count those cells which contain a mix of valid and invalid characters.

For "Not Valid"=COUNTIFS(E4:H4,"<>M",E**A*",E4:H4,"<>E",E4:H4,"<>N")-T4*

T4 = cell under Blank

For "Morning" etc: =COUNTIF(E4:H4,"M")

Answer 1

User's image

Hi @Michael H,

Try below formula in cell E4 (see the screenshot above).

=LET(
data, A4:D7,
daypart, {"m";"a";"e";"n"},
HSTACK(BYROW(data, LAMBDA(a, SUM(--ISBLANK(a)))),
       BYROW(data, COUNTA),
       BYROW(data, LAMBDA(b, SUM(BYCOL(b, LAMBDA(a, --NOT(BYROW(BYCOL(IFNA(REGEXEXTRACT(a, ".", 1) = daypart, TRUE), OR), OR))))))),
       BYROW(data, LAMBDA(b, SUM(IFNA(BYCOL(b, LAMBDA(a, --BYCOL(BYROW(--BYCOL(REGEXEXTRACT(a, ".", 1) = daypart, OR) = {0;1}, OR), AND))), "")))),
       IFERROR(--TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",, BYCOL(I2:L2, LAMBDA(c, ARRAYTOTEXT(BYROW(data, LAMBDA(b, SUM(--BYCOL(IFNA(TEXTSPLIT(TEXTJOIN(";",,
               IFNA(BYCOL(b, LAMBDA(a, ARRAYTOTEXT(REGEXEXTRACT(a, ".", 1)))), "")), ", ", ";"), "") = LEFT(c, 1), OR)))))))), ", ", ";")), 0))
)

Change the range in formula as per you need.

HTH

IlirU

Answer 2

Ashish Mathur 101.8K Volunteer Moderator

Hi,

In cell G3, enter this formula

=SUM(1-(1*(BYCOL(A3:D3,LAMBDA(b,LET(a,XMATCH(REGEXEXTRACT(b,".",1),{"m","a","e","n"}),AND(COUNT(a)=LEN(b),LEN(b)>0)))))))

Hope this helps.

User's image

0 comments

Answer 3

EmilyS726 228.2K Independent Advisor

Hello

Are the valid and invalid characters limited to a certain set of letters/symbols only? Or is it safe to say, you basically want to count any cell that's not blank? If so, you can use the =COUNTA() function.

EmilyS726 228.2K Reputation points Independent Advisor

2026-05-04T02:33:16.3766667+00:00

Let me know if you can clarify the question I asked.
EmilyS726 228.2K Reputation points Independent Advisor

2026-05-04T13:10:55+00:00
Based on your clarification, this is what I would use, seems to work when I tested in mine:

=SUM(MAP(E4:H4,LAMBDA(x,--AND(x<>"",SUM(--ISNUMBER(SEARCH({"M","A","E","N"},UPPER(x))))>0,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(x),"M",""),"A",""),"E",""),"N",""))>0))))

Answer 4

Use COUNTIFS with two complementary criteria on the same range: one to ensure the cell contains at least one valid character, and another to ensure it also contains at least one invalid character.

Assume:

The cells to test are in A1:A100.
Valid characters are only a, b, c, d (case-insensitive).

First, define what “invalid” means: any character that is not a–d. Since COUNTIF/COUNTIFS work with patterns, use wildcards to detect presence of valid and invalid characters.
Example approach (requires helper columns because COUNTIFS cannot directly express “not in this set of characters”):
- In B1, test if the cell contains at least one valid character:
```
     =OR(ISNUMBER(SEARCH("a",A1)),ISNUMBER(SEARCH("b",A1)),ISNUMBER(SEARCH("c",A1)),ISNUMBER(SEARCH("d",A1)))
```
- In C1, test if the cell contains at least one invalid character by stripping valid characters and checking what remains. For example, using nested SUBSTITUTE:
```
     =LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a",""),"b",""),"c",""),"d",""))>0
```
  This removes all a, b, c, d and checks if any characters are left.
- Copy B1:C1 down to row 100.
Finally, count cells that have both valid and invalid characters:
```
   =COUNTIFS(B1:B100,TRUE,C1:C100,TRUE)
```

This counts cells where:

At least one valid character (a–d) is present, and
At least one other (invalid) character is also present.

To count cells that contain only valid characters, use:

=COUNTIFS(B1:B100,TRUE,C1:C100,FALSE)

To count cells that contain only invalid characters, use:

=COUNTIFS(B1:B100,FALSE,C1:C100,TRUE)

Adjust the SUBSTITUTE list to match the exact set of valid characters required.

References:

Share via

Excel - Counting cells according to content.

4 answers

Your answer