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

excel excel-2007 worksheet-function countif
Kim picture Kim · Jul 2, 2012 · Viewed 11.3k times · Source

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.

Answer

Siddharth Rout picture Siddharth Rout · Jul 2, 2012

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"

Related questions

Compare data of two Excel Columns A & B, and show data of Column A that do not exist in B

I have an Excel file that has columns A and B, both have data that may or may not exist in other column, I'm only interested in the data of column A that do not exist in B. These Columns …

Read More
Comparing two columns in Excel with exclusion

I want to compare values in two columns in Excel as depicted in the image below :- Using the formula, I want to put the values in the "Values of A which don't exist in B" and "Values of B …

Read More
Append same text to every cell in a column in Excel

How can I append text to every cell in a column in Excel? I need to add a comma (",") to the end. Example: [email protected] turns into [email protected], Data Sample: [email protected] satishmm_2sptc@…

Read More