CODEWITHSUNDEEP
excelexcel-2007worksheet-functioncountif
Kim
Kim

Reputation: 2777

How do I count not equal to wildcard characters in Excel?

Using COUNTIF(range,"<>") can I count non-empty cells and with COUNTIF(range,"~?") or COUNTIF(range,"~*") can I calculate the number of cells matching a wildcard character, but can I do it with one COUNTIF() ?

COUNTIF(range,"<>~?") does not work.

Upvotes: 3

Views: 14329

Answers (3)

T. I. Troll
T. I. Troll

Reputation: 11

this may have been just fixed in Excel 2010: =COUNTIF(H:H,"<>~?") correctly counts only non-? values in column H (so ? and '? reduce the count, but quoted "?", A, etc all don't)

Upvotes: 0

barry houdini
barry houdini

Reputation: 46331

My understanding here is that you want to count cells in a range that satisfy two conditions - 1) they are not blank, 2) they are not "?"

If you are using Excel 2007 or later try COUNTIFS (with an "S" on the end) with both conditions like this

=COUNTIFS(range,"<>",range,"<>~?")

or for earlier versions use SUMPRODUCT

=SUMPRODUCT((range<>"")*(range<>"?"))

[Note: you don't need ~ in the latter]

In Excel 2003 or earlier range can't be a whole column. You can use the same approach for *

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149287

Using COUNTIF(range,"<>") can I count non-empty cells

You can use COUNTA() to Count Non Empty Cells. 

=COUNTA(A1:A10)

If you still want to use COUNTIF() then yes, you can use it like

=COUNTIF(A1:A10,"<>")

You can use wildcards to count for cells which have specific words like this

=COUNTIF(A1:A10,"Kim*")   '<~~ Starts with "Kim"
=COUNTIF(A1:A10,"*Kim*")  '<~~ Contains "Kim"
=COUNTIF(A1:A10,"*Kim")   '<~~ Ends with "Kim"

To count Not Equal to With WildCards, you can use this

=COUNTIF(A1:A10,"<>Kim*")   '<~~ Doesn't Start with "Kim"

Upvotes: 2

Related Questions