Excel- How to find the count excluding invalid characters

Question

How to find the invalid records in a column in excel. Invalid records in my example is having special characters not blanks in the columns

E.g : in a column I am having dates from A1: A10 and in A5 & A6 I will have &&&& and %%%%, I want a formula where it should give me a count and result is 8 excluding these two special characters values.

1/1/2013
1/2/2013
1/3/2013
1/4/2013
&&&&&
%%%%%
1/7/2013
1/8/2013
1/9/2013
1/10/2013

Any suggestions please?

Could you put the formula you were trying to use in your question? — Jerry, Sep 25 '13 at 16:38
Perhaps `COUNTA(A:A)-COUNT(A:A)` to get the count of invalid entries — tigeravatar, Sep 25 '13 at 16:41
To get the count of valid entries (dates), it would just be `COUNT(A:A)` — tigeravatar, Sep 25 '13 at 16:42
I got it =COUNTIFS(Q18:Q27,"*"), but the question is how it is excluding dates and giving the result — nagendra, Sep 25 '13 at 16:43
If you use the simple `COUNT` it will only count numbers, dates are numbers also, symbols are not. It you did `=Count(ColumnYouHave)` it would return 8 — user2140261, Sep 25 '13 at 16:43
`=COUNTIFS(Q18:Q27,"*")` will count all the cells having text values in them. Use `COUNT()` as already suggested by the other comments. — Jerry, Sep 25 '13 at 16:48

Automate This · Answer 1 · 2020-11-04T15:23:39.393

1

This formula will count all cells that don't equal &&&&& or %%%%% including blanks.

=COUNTIFS(A1:A10,"<>&&&&&",A1:A10,"<>%%%%%")

This formula will count all cells that don't equal &&&&& or %%%%% excluding blanks.

 =SUMPRODUCT(COUNT(A1:A10)-(COUNTIF(A1:A10,"<>&&&&&")-COUNTIF(A1:A10,"<>%%%%%")))

enter image description here

edited Nov 04 '20 at 15:23

answered Sep 25 '13 at 16:41

Automate This

30,726
11
60
82

This is a bit too complicated IMO =P – Jerry Sep 25 '13 at 16:43
1

Albeit this is a long ago answer, to add info as the responder didn't. The '<>' means non-blank cells. You also can, as is in the COUNTIFS formula, add character you also don't want to count to the right of the <> in the double quotes. So essentially, the first formula is *count all non-blank cells that also don't have &&&&& or %%%%%* – Miniman Nov 04 '20 at 14:55
Hi @Miniman - thanks for adding additional info to my answer to help improve it. One small change... I believe '<>' is technically "does not equal" rather than "non-blank". The first formula will count blanks but the second formula won't. – Automate This Nov 04 '20 at 15:21
Automate This, @Miniman [is right](https://stackoverflow.com/a/772267/2752308); this non/poor-documented trick, however, counts ="" as non-empty – Marcelo Scofano Diniz Jan 14 '21 at 01:35
@MarceloScofanoDiniz - Good to know... Thanks for the link/tip – Automate This Jan 14 '21 at 02:57

Excel- How to find the count excluding invalid characters

1 Answers1