How do I use wild cards in nested array formulas?

[hopeit]

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.

 

[Biff]

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

 

[Bob Phillips]

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

still an array formula

--

HTH

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

 

[hopeit]

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

 

[hopeit]

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

Hopeit

 

[Biff]

Care to elaborate?

Biff

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

Hopeit

 

[hopeit]

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]

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