COUNT or COUNTIF using wildcard text?

Hello,

I am trying to create a formula that will count the number of cells with
certain text (EP) in one range that also have an instance of certain wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to work, and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not used for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the text
in S16:S101 will be changed to "migrated" or "replaced & migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula? Apparently,
the wildcard text thing only seems to work for criteria in simpler formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to figure
this one out.

Thank you!

Mike

Try this:

=SUMPRODUCT(--(N17:N120="ep"),--(ISNUMBER(SEARCH("migrate",S17:S120))))

Biff

"WiFiMike2006" wrote in message
news
Hello,

I am trying to create a formula that will count the number of cells with
certain text (EP) in one range that also have an instance of certain
wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to work,
and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not used
for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the text
in S16:S101 will be changed to "migrated" or "replaced & migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula?
Apparently,
the wildcard text thing only seems to work for criteria in simpler
formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to figure
this one out.

Thank you!

Mike

Another possibilty....DCOUNTA?

With
N16:N120 contains your first list, with List_1 in N16
S16:S120 contains your second list, with List_2 in S16

K1: List_1
K2: EP

L1: List_2
L2: *migrate*

M1: =DCOUNTA(N16:S120,"List_1",K1:L2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

Hello,

I am trying to create a formula that will count the number of cells with
certain text (EP) in one range that also have an instance of certain wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to work, and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not used for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the text
in S16:S101 will be changed to "migrated" or "replaced & migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula? Apparently,
the wildcard text thing only seems to work for criteria in simpler formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to figure
this one out.

Thank you!

Mike

I'm not quite sure what I need to do for this to work. The ranges i gave in
the original post have various text in all the cells. Are you saying I need
to enter a list of something in certain cells? Can you please give me a
little more of a step-by-step on this? I've never even heard of DCOUNTA
before, let alone used it.

Thanks!
Mike

"Ron Coderre" wrote:

Another possibilty....DCOUNTA?

With
N16:N120 contains your first list, with List_1 in N16
S16:S120 contains your second list, with List_2 in S16

K1: List_1
K2: EP

L1: List_2
L2: *migrate*

M1: =DCOUNTA(N16:S120,"List_1",K1:L2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

Hello,

I am trying to create a formula that will count the number of cells with
certain text (EP) in one range that also have an instance of certain wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to work, and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not used for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the text
in S16:S101 will be changed to "migrated" or "replaced & migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula? Apparently,
the wildcard text thing only seems to work for criteria in simpler formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to figure
this one out.

Thank you!

Mike

Wow. I have no idea how those functions work, but it worked!

THANK YOU! YOU RULE!

Mike

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(N17:N120="ep"),--(ISNUMBER(SEARCH("migrate",S17:S120))))

Biff

"WiFiMike2006" wrote in message
news
Hello,

I am trying to create a formula that will count the number of cells with
certain text (EP) in one range that also have an instance of certain
wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to work,
and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not used
for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the text
in S16:S101 will be changed to "migrated" or "replaced & migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula?
Apparently,
the wildcard text thing only seems to work for criteria in simpler
formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to figure
this one out.

Thank you!

Mike

Sorry about the confusion....

Regarding: =DCOUNTA(N16:S120,"List_1",K1:L2)

Excel has several functions specifically made to work with data lists
("databases"). DCOUNTA returns the count of non-blank items in a specified
column of a database that match a criteria.

It has this format:
=DCOUNTA(database,fieldname_to_count_values_in,ran ge_containing_criteria)

In your example....you have 2 lists of values:
N17:N120
and S17:S120

Since database need column headings,
I arbitrarily put "List_1" in N16 and "List_2" in S16

For the first parameter of the function
I used N16:S120
.....Since we're not using columns O,P,Q and R
It doesn't matter what's in them or that they don't have column headings

For the second parameter of the function
I entered "List_1"...the column heading of the range to count

For the third parameter, the criteria,
I referenced the range K1:L2
.....That range must have a certain format.
The top row of cells MUST contain a column title from the database
The cells below those titles contain the pertinent patterns to match.
K2: EP.....so List_1 items must only have "EP" in them
L2: *migrate*....so List_2 items must *contain* the word "migrate"

(criteria can get somewhat complicated but your needs did not require
anything fancy)

Summary:
the formula starts with the database: N16:S120
Finds all instances where List_1 = "EP" AND List_2 contains "migrate"
and returns the count of non-blank items from List_1

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

I'm not quite sure what I need to do for this to work. The ranges i gave in
the original post have various text in all the cells. Are you saying I need
to enter a list of something in certain cells? Can you please give me a
little more of a step-by-step on this? I've never even heard of DCOUNTA
before, let alone used it.

Thanks!
Mike

"Ron Coderre" wrote:

Another possibilty....DCOUNTA?

With
N16:N120 contains your first list, with List_1 in N16
S16:S120 contains your second list, with List_2 in S16

K1: List_1
K2: EP

L1: List_2
L2: *migrate*

M1: =DCOUNTA(N16:S120,"List_1",K1:L2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

Hello,

I am trying to create a formula that will count the number of cells with
certain text (EP) in one range that also have an instance of certain wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to work, and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not used for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the text
in S16:S101 will be changed to "migrated" or "replaced & migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula? Apparently,
the wildcard text thing only seems to work for criteria in simpler formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to figure
this one out.

Thank you!

Mike

You're welcome. Thanks for the feedback!

Biff

"WiFiMike2006" wrote in message
...
Wow. I have no idea how those functions work, but it worked!

THANK YOU! YOU RULE!

Mike

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(N17:N120="ep"),--(ISNUMBER(SEARCH("migrate",S17:S120))))

Biff

"WiFiMike2006" wrote in message
news
Hello,

I am trying to create a formula that will count the number of cells
with
certain text (EP) in one range that also have an instance of certain
wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to
work,
and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not
used
for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the
text
in S16:S101 will be changed to "migrated" or "replaced & migrated",
which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula?
Apparently,
the wildcard text thing only seems to work for criteria in simpler
formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to
figure
this one out.

Thank you!

Mike

Ok. Now here's a similar problem, but a little more complicated.

For example, let's say this is the data range:

A B C D
16 cam unit
17 PG W12
18 ICS X5
19 ICS W22
20 474 X7
21 474 W15b
22 1390
23 1390 W6
24 PG

What would I need to do to get a count in cell D16 of only the number of
ICS, 474, and 1390 in the "cam" column that DO NOT have text *containing* the
letter "W" in the "unit" column? For this example, the result in D16 would be
3.

I hope you can answer this one too.

Thank you,
Mike

"Ron Coderre" wrote:

Sorry about the confusion....

Regarding: =DCOUNTA(N16:S120,"List_1",K1:L2)

Excel has several functions specifically made to work with data lists
("databases"). DCOUNTA returns the count of non-blank items in a specified
column of a database that match a criteria.

It has this format:
=DCOUNTA(database,fieldname_to_count_values_in,ran ge_containing_criteria)

In your example....you have 2 lists of values:
N17:N120
and S17:S120

Since database need column headings,
I arbitrarily put "List_1" in N16 and "List_2" in S16

For the first parameter of the function
I used N16:S120
....Since we're not using columns O,P,Q and R
It doesn't matter what's in them or that they don't have column headings

For the second parameter of the function
I entered "List_1"...the column heading of the range to count

For the third parameter, the criteria,
I referenced the range K1:L2
....That range must have a certain format.
The top row of cells MUST contain a column title from the database
The cells below those titles contain the pertinent patterns to match.
K2: EP.....so List_1 items must only have "EP" in them
L2: *migrate*....so List_2 items must *contain* the word "migrate"

(criteria can get somewhat complicated but your needs did not require
anything fancy)

Summary:
the formula starts with the database: N16:S120
Finds all instances where List_1 = "EP" AND List_2 contains "migrate"
and returns the count of non-blank items from List_1

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

I'm not quite sure what I need to do for this to work. The ranges i gave in
the original post have various text in all the cells. Are you saying I need
to enter a list of something in certain cells? Can you please give me a
little more of a step-by-step on this? I've never even heard of DCOUNTA
before, let alone used it.

Thanks!
Mike

"Ron Coderre" wrote:

Another possibilty....DCOUNTA?

With
N16:N120 contains your first list, with List_1 in N16
S16:S120 contains your second list, with List_2 in S16

K1: List_1
K2: EP

L1: List_2
L2: *migrate*

M1: =DCOUNTA(N16:S120,"List_1",K1:L2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

Hello,

I am trying to create a formula that will count the number of cells with
certain text (EP) in one range that also have an instance of certain wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to work, and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not used for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the text
in S16:S101 will be changed to "migrated" or "replaced & migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula? Apparently,
the wildcard text thing only seems to work for criteria in simpler formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to figure
this one out.

Thank you!

Mike

Hi

Try
=SUMPRODUCT(($A$17:$A$24={"ICS," "474", "1390"})*
(NOT(ISNUMBER(SEARCH($B$17:$B$24,"W")))))


--
Regards

Roger Govier


"WiFiMike2006" wrote in message
...
Ok. Now here's a similar problem, but a little more complicated.

For example, let's say this is the data range:

A B C D
16 cam unit
17 PG W12
18 ICS X5
19 ICS W22
20 474 X7
21 474 W15b
22 1390
23 1390 W6
24 PG

What would I need to do to get a count in cell D16 of only the number
of
ICS, 474, and 1390 in the "cam" column that DO NOT have text
*containing* the
letter "W" in the "unit" column? For this example, the result in D16
would be
3.

I hope you can answer this one too.

Thank you,
Mike

"Ron Coderre" wrote:

Sorry about the confusion....

Regarding: =DCOUNTA(N16:S120,"List_1",K1:L2)

Excel has several functions specifically made to work with data lists
("databases"). DCOUNTA returns the count of non-blank items in a
specified
column of a database that match a criteria.

It has this format:
=DCOUNTA(database,fieldname_to_count_values_in,ran ge_containing_criteria)

In your example....you have 2 lists of values:
N17:N120
and S17:S120

Since database need column headings,
I arbitrarily put "List_1" in N16 and "List_2" in S16

For the first parameter of the function
I used N16:S120
....Since we're not using columns O,P,Q and R
It doesn't matter what's in them or that they don't have column
headings

For the second parameter of the function
I entered "List_1"...the column heading of the range to count

For the third parameter, the criteria,
I referenced the range K1:L2
....That range must have a certain format.
The top row of cells MUST contain a column title from the database
The cells below those titles contain the pertinent patterns to match.
K2: EP.....so List_1 items must only have "EP" in them
L2: *migrate*....so List_2 items must *contain* the word "migrate"

(criteria can get somewhat complicated but your needs did not require
anything fancy)

Summary:
the formula starts with the database: N16:S120
Finds all instances where List_1 = "EP" AND List_2 contains "migrate"
and returns the count of non-blank items from List_1

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

I'm not quite sure what I need to do for this to work. The ranges i
gave in
the original post have various text in all the cells. Are you
saying I need
to enter a list of something in certain cells? Can you please give
me a
little more of a step-by-step on this? I've never even heard of
DCOUNTA
before, let alone used it.

Thanks!
Mike

"Ron Coderre" wrote:

Another possibilty....DCOUNTA?

With
N16:N120 contains your first list, with List_1 in N16
S16:S120 contains your second list, with List_2 in S16

K1: List_1
K2: EP

L1: List_2
L2: *migrate*

M1: =DCOUNTA(N16:S120,"List_1",K1:L2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

Hello,

I am trying to create a formula that will count the number of
cells with
certain text (EP) in one range that also have an instance of
certain wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem
to work, and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text
not used for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that
sometimes the text
in S16:S101 will be changed to "migrated" or "replaced &
migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula?
Apparently,
the wildcard text thing only seems to work for criteria in
simpler formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying
to figure
this one out.

Thank you!

Mike

Hi, Mike

What you posted is a good example for the DCOUNTA function

So....with your posted data list in A16:B24

Here's the set up

A1: cam
A2: ICS
A3: 474
A4: 1390

B1: unit
B2: *W*
B3: *W*
B4: *W*

You'll end up with a grid that looks like this:
cam unit
ICS *W*
474 *W*
1390 *W*

Interpret that this way.....
Items in the same row are AND (eg cam=ICS AND unit*W*)
Items on different rows are OR

That grid means
(eg cam=ICS AND unit*W*)
OR
(eg cam=474 AND unit*W*)
OR
(eg cam=1390 AND unit*W*)

The formula in D16 would be:
=DCOUNTA(A16:B24,A16,A1:B4)

....and the returned value: 3

Is that something you can work with?

Post back with any other questions.
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

Ok. Now here's a similar problem, but a little more complicated.

For example, let's say this is the data range:

A B C D
16 cam unit
17 PG W12
18 ICS X5
19 ICS W22
20 474 X7
21 474 W15b
22 1390
23 1390 W6
24 PG

What would I need to do to get a count in cell D16 of only the number of
ICS, 474, and 1390 in the "cam" column that DO NOT have text *containing* the
letter "W" in the "unit" column? For this example, the result in D16 would be
3.

I hope you can answer this one too.

Thank you,
Mike

"Ron Coderre" wrote:

Sorry about the confusion....

Regarding: =DCOUNTA(N16:S120,"List_1",K1:L2)

Excel has several functions specifically made to work with data lists
("databases"). DCOUNTA returns the count of non-blank items in a specified
column of a database that match a criteria.

It has this format:
=DCOUNTA(database,fieldname_to_count_values_in,ran ge_containing_criteria)

In your example....you have 2 lists of values:
N17:N120
and S17:S120

Since database need column headings,
I arbitrarily put "List_1" in N16 and "List_2" in S16

For the first parameter of the function
I used N16:S120
....Since we're not using columns O,P,Q and R
It doesn't matter what's in them or that they don't have column headings

For the second parameter of the function
I entered "List_1"...the column heading of the range to count

For the third parameter, the criteria,
I referenced the range K1:L2
....That range must have a certain format.
The top row of cells MUST contain a column title from the database
The cells below those titles contain the pertinent patterns to match.
K2: EP.....so List_1 items must only have "EP" in them
L2: *migrate*....so List_2 items must *contain* the word "migrate"

(criteria can get somewhat complicated but your needs did not require
anything fancy)

Summary:
the formula starts with the database: N16:S120
Finds all instances where List_1 = "EP" AND List_2 contains "migrate"
and returns the count of non-blank items from List_1

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

I'm not quite sure what I need to do for this to work. The ranges i gave in
the original post have various text in all the cells. Are you saying I need
to enter a list of something in certain cells? Can you please give me a
little more of a step-by-step on this? I've never even heard of DCOUNTA
before, let alone used it.

Thanks!
Mike

"Ron Coderre" wrote:

Another possibilty....DCOUNTA?

With
N16:N120 contains your first list, with List_1 in N16
S16:S120 contains your second list, with List_2 in S16

K1: List_1
K2: EP

L1: List_2
L2: *migrate*

M1: =DCOUNTA(N16:S120,"List_1",K1:L2)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"WiFiMike2006" wrote:

Hello,

I am trying to create a formula that will count the number of cells with
certain text (EP) in one range that also have an instance of certain wildcard
text (*migrate*) in another range in the same row.

For example, this is the formula I'm using, but it doesn't seem to work, and
I need to know what I should use instead:

=COUNT(IF((N17:N120="EP")*(S17:S120="*migrate*"),0 ))

However, the formula works if it looks like this (wildcard text not used for
"migrate"):

=COUNT(IF((N16:N101="EF")*(S16:S101="migrate"),0))

But, the reason I need to use the wildcard text is that sometimes the text
in S16:S101 will be changed to "migrated" or "replaced & migrated", which
throws off the count resulting from the formula.

Is there a way to do this with a different function or formula? Apparently,
the wildcard text thing only seems to work for criteria in simpler formulas
like this one:

=COUNTIF($O$16:$O$105, "*SWM*")

Any help would be greatly appreciated. I've spent hours trying to figure
this one out.

Thank you!

Mike