CODEWITHSUNDEEP
excelwildcardcountif
Dan K
Dan K

Reputation: 21

Trying to specify multiple wildcards in a countifs function for Excel

I am working on a data sheet that has almost 300,000 rows by about 40 columns.
I have a countifs function to count the number of rows that have an entry ranging from "A1" through "A5" for each letter A-G in a particular column.

I have broken out analysis on separate sheets to pull data for each row for each separate letter A-G using countifs(range,"other data","F?") (I know its simplified).

I need to create a new sheet that excludes any row with an A value in it.
I tried countifs(range,"other data", range,{"B?","C?","D?","E?","F?","G?"}) and it only returns the count for the outside values (B and G), how do I get Excel to count all of those other values as well? I would like to keep this format because to create the sheets for B-G, I just used the find and replace to replace "A?" with "B?" and so on for the other sheets.

I would like to just replace "B?" with whatever works to count the number of rows that have B-G in that particular column.

Upvotes: 2

Views: 1160

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You countifs formula, with an array constant for criteria, returns an array of values. But what you want is the SUM of that array. So:

sum(countifs(range,"other data", range,{"B?","C?","D?","E?","F?","G?"}))

Without the sum function, you will only see the value of the first element of that array.

Upvotes: 2

Tom Sharpe
Tom Sharpe

Reputation: 34180

I have a feeling this is the wrong answer, but I'll say it anyway. Why can't you use

=COUNTIFS(Range,"<>A?")

Or are there other possible values that you want to exclude?

In which case you should be able to use this for A

=COUNTIFS(Range,">=B1",Range,"<=G5")

and for B1-B5

=COUNTIFS(Range,">=A1",Range,"<=A5")+COUNTIFS(Range,">=C1",Range,"<=G5")

which can be modified for C, D, E and F

and this for G

=COUNTIFS(Range,">=G1",Range,"<=G5")

Upvotes: 0

Related Questions