I have a spread sheet that has several columns. I have created a nested array
formula to tell me how many items fall into my criteria, but I am missing
some information, see fomula below:


{=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*('NON-APPLICABLE'!AP3:AP2113="No
draw or progressive, plastic injection molds"),'NON-APPLICABLE'!E3:E2113))}

My problem is that I may or may not add information into the column
containing the statement "No draw or progressive..." I need to know how to
get a wild card to work after the word "molds". With the statement above, it
counts just that and nothing else. I can get the wild card to work in any
other formula.

Hi!

Try this:

Normally entered

=SUMPRODUCT(--('NON-APPLICABLE'!I3:I2113="MI"),--(ISNUMBER(SEARCH("No draw
or progressive, plastic injection
molds",'NON-APPLICABLE'!AP3:AP2113))),--(ISNUMBER('NON-APPLICABLE'!E3:E2113)))

Biff

"hopeit" wrote in message
...
I have a spread sheet that has several columns. I have created a nested
array
formula to tell me how many items fall into my criteria, but I am missing
some information, see fomula below:


{=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*('NON-APPLICABLE'!AP3:AP2113="No
draw or progressive, plastic injection
molds"),'NON-APPLICABLE'!E3:E2113))}

My problem is that I may or may not add information into the column
containing the statement "No draw or progressive..." I need to know how to
get a wild card to work after the word "molds". With the statement above,
it
counts just that and nothing else. I can get the wild card to work in any
other formula.

Thanks Biff! Thak seems to have takenm care of my issue and all seems to be
working with my quick test. If I have problems when I get the formula in
place, I'll let you know.

Hopeit

"Biff" wrote:

Hi!

Try this:

Normally entered

=SUMPRODUCT(--('NON-APPLICABLE'!I3:I2113="MI"),--(ISNUMBER(SEARCH("No draw
or progressive, plastic injection
molds",'NON-APPLICABLE'!AP3:AP2113))),--(ISNUMBER('NON-APPLICABLE'!E3:E2113)))

Biff

"hopeit" wrote in message
...
I have a spread sheet that has several columns. I have created a nested
array
formula to tell me how many items fall into my criteria, but I am missing
some information, see fomula below:


{=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*('NON-APPLICABLE'!AP3:AP2113="No
draw or progressive, plastic injection
molds"),'NON-APPLICABLE'!E3:E2113))}

My problem is that I may or may not add information into the column
containing the statement "No draw or progressive..." I need to know how to
get a wild card to work after the word "molds". With the statement above,
it
counts just that and nothing else. I can get the wild card to work in any
other formula.

OOPS, I spoke too soon. It's still not counting every instance of what I need
to count.

Hopeit

"Biff" wrote:

Hi!

Try this:

Normally entered

=SUMPRODUCT(--('NON-APPLICABLE'!I3:I2113="MI"),--(ISNUMBER(SEARCH("No draw
or progressive, plastic injection
molds",'NON-APPLICABLE'!AP3:AP2113))),--(ISNUMBER('NON-APPLICABLE'!E3:E2113)))

Biff

"hopeit" wrote in message
...
I have a spread sheet that has several columns. I have created a nested
array
formula to tell me how many items fall into my criteria, but I am missing
some information, see fomula below:


{=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*('NON-APPLICABLE'!AP3:AP2113="No
draw or progressive, plastic injection
molds"),'NON-APPLICABLE'!E3:E2113))}

My problem is that I may or may not add information into the column
containing the statement "No draw or progressive..." I need to know how to
get a wild card to work after the word "molds". With the statement above,
it
counts just that and nothing else. I can get the wild card to work in any
other formula.

Care to elaborate?

Biff

"hopeit" wrote in message
...
OOPS, I spoke too soon. It's still not counting every instance of what I
need
to count.

Hopeit

"Biff" wrote:

Hi!

Try this:

Normally entered

=SUMPRODUCT(--('NON-APPLICABLE'!I3:I2113="MI"),--(ISNUMBER(SEARCH("No
draw
or progressive, plastic injection
molds",'NON-APPLICABLE'!AP3:AP2113))),--(ISNUMBER('NON-APPLICABLE'!E3:E2113)))

Biff

"hopeit" wrote in message
...
I have a spread sheet that has several columns. I have created a nested
array
formula to tell me how many items fall into my criteria, but I am
missing
some information, see fomula below:


{=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*('NON-APPLICABLE'!AP3:AP2113="No
draw or progressive, plastic injection
molds"),'NON-APPLICABLE'!E3:E2113))}

My problem is that I may or may not add information into the column
containing the statement "No draw or progressive..." I need to know how
to
get a wild card to work after the word "molds". With the statement
above,
it
counts just that and nothing else. I can get the wild card to work in
any
other formula.

It will only count the items that have the phrase:

"No draw or progressive, plastic injection molds"

If I want to add a comment to the line, such as:

", referred Tom Jones",

it will not count that.

I need to know if the use of wild cards is allowed within a nested array
formula. So far I can not get that to work.

Thanks,
Hopeit

"Biff" wrote:

Care to elaborate?

Biff

"hopeit" wrote in message
...
OOPS, I spoke too soon. It's still not counting every instance of what I
need
to count.

Hopeit

"Biff" wrote:

Hi!

Try this:

Normally entered

=SUMPRODUCT(--('NON-APPLICABLE'!I3:I2113="MI"),--(ISNUMBER(SEARCH("No
draw
or progressive, plastic injection
molds",'NON-APPLICABLE'!AP3:AP2113))),--(ISNUMBER('NON-APPLICABLE'!E3:E2113)))

Biff

"hopeit" wrote in message
...
I have a spread sheet that has several columns. I have created a nested
array
formula to tell me how many items fall into my criteria, but I am
missing
some information, see fomula below:


{=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*('NON-APPLICABLE'!AP3:AP2113="No
draw or progressive, plastic injection
molds"),'NON-APPLICABLE'!E3:E2113))}

My problem is that I may or may not add information into the column
containing the statement "No draw or progressive..." I need to know how
to
get a wild card to work after the word "molds". With the statement
above,
it
counts just that and nothing else. I can get the wild card to work in
any
other formula.

Hi!

If you have in cells:

A1 = There is no draw or progressive, plastic injection molds

A2 = No draw or progressive, plastic injection molds, referred Tom Jones

A3 = There is no draw or progressive, plastic injection molds, referred Tom
Jones

The suggested formula WILL count all 3 of those cells. (provided the other
criteria in the formula are met).

If the formula is not counting those cells some other criteria may not be
being met.

I need to know if the use of wild cards is allowed within a nested array

No. Not in the type of formula you need.

Want me to take a look at your file?

Biff

"hopeit" wrote in message
...
It will only count the items that have the phrase:

"No draw or progressive, plastic injection molds"

If I want to add a comment to the line, such as:

", referred Tom Jones",

it will not count that.

I need to know if the use of wild cards is allowed within a nested array
formula. So far I can not get that to work.

Thanks,
Hopeit

"Biff" wrote:

Care to elaborate?

Biff

"hopeit" wrote in message
...
OOPS, I spoke too soon. It's still not counting every instance of what
I
need
to count.

Hopeit

"Biff" wrote:

Hi!

Try this:

Normally entered

=SUMPRODUCT(--('NON-APPLICABLE'!I3:I2113="MI"),--(ISNUMBER(SEARCH("No
draw
or progressive, plastic injection
molds",'NON-APPLICABLE'!AP3:AP2113))),--(ISNUMBER('NON-APPLICABLE'!E3:E2113)))

Biff

"hopeit" wrote in message
...
I have a spread sheet that has several columns. I have created a
nested
array
formula to tell me how many items fall into my criteria, but I am
missing
some information, see fomula below:


{=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*('NON-APPLICABLE'!AP3:AP2113="No
draw or progressive, plastic injection
molds"),'NON-APPLICABLE'!E3:E2113))}

My problem is that I may or may not add information into the column
containing the statement "No draw or progressive..." I need to know
how
to
get a wild card to work after the word "molds". With the statement
above,
it
counts just that and nothing else. I can get the wild card to work
in
any
other formula.

=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*(ISNUMBER(FIND("No draw or
progressive, plastic injection
molds",'NON-APPLICABLE'!P3:P2113))),'NON-APPLICABLE'!E3:E2113))

still an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)


"hopeit" wrote in message
...
I have a spread sheet that has several columns. I have created a nested
array
formula to tell me how many items fall into my criteria, but I am missing
some information, see fomula below:



{=COUNT(IF(('NON-APPLICABLE'!I3:I2113="MI")*('NON-APPLICABLE'!AP3:AP2113="No
draw or progressive, plastic injection
molds"),'NON-APPLICABLE'!E3:E2113))}

My problem is that I may or may not add information into the column
containing the statement "No draw or progressive..." I need to know how to
get a wild card to work after the word "molds". With the statement above,
it
counts just that and nothing else. I can get the wild card to work in any
other formula.