How do I use wild cards in nested array formulas?

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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.
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
>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.