Wildcard not working in SumProduct Array

[Roady]

Hello:

I am trying to accomplish a SUMPRODUCT (count as 1) if a line of data meets
the following criteria:
C=205
D=Australia
F=Yarn
G contains -
H contains -
I contains -
M=zero

Here's the formula I am using - most pieces are working except the portion
regarding columns G/H/I
=SUMPRODUCT(--(C17:C65535=205),--(D1765535="Australia"),--(F17:F65535="Yarn"),--(G17:G65535="*-*"),--(H17:H65535="*-*"),--(I17:I65535="*-*"),--(M17:M65535=0))

I have tested it and it seems to work until I add the G/H/I columns with a
wildcard in. The formula DID work when I had G/H/I equal a specific value
that was not a wildcard.

Any help is appreciated! Thank you!!

 

[Don Guillett]

More info on your layout

 

[Mike H]

Hi,

Sumproduct can't use wildcards so a different approach is required. Also if
you can you should shorten your ranges

=SUMPRODUCT(--(C17:C65535=205),--(D1765535="Australia"),--(F17:F65535="Yarn"),--(ISNUMBER(SEARCH("-",G17:G65535))),--(ISNUMBER(SEARCH("-",H17:H65535))),--(ISNUMBER(SEARCH("-",I17:I65535))),--(M17:M65535=0))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Roady]

Don - are you asking for more info about the layout of my spreadsheet?

It has several columns A-R - each column has a title header with an
auto-filter.
End-users enter data in the rows below. Each row represents a unique product.
I want to be able to analyze each row by the parameters listed below so that
it wil count as 1 if it meets all requirements. Then sum all the rows that
meet that requirement. So if 4 of 10 rows meet the requirements, then it
yields a count of 4. Therefore 4 products met the approval criteria.

does that help? Let me know if you have more specific questions too that I
can answer.

thanks!

 

[Roady]

I am curious why you suggest that should I shorten the ranges? Does it affect
the accuracy or is it speed you are concerned with? The sheet has column
headers with auto-filters and then end-users enter in data in the rows below.
So I wanted to be able to account for any row they may enter into - does that
make sense?

 

[Roady]

p.s. Mike - you're a genius!!! This totally works! you made my week!
Thanks!!

 

[Mike H]

Hi,

Glad i could help.

No issues with accuracy, if it works it works but particulary with E2007 it
can cause speed issues if you reference a full column or as in this case
nearly a full column

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Shane Devenshire]

Mike has given you Ochem's razor (I probably misspelled Ochem).

 

[Don Guillett]

I would suggest using self adjusting defined name ranges instead so you only
calculate what is necessary.
While on the sheet with the ranges>insert>name>define>name colC>in the
refers to box
=offset($c$17,0,0,counta($c:$c)-16,1)
for colG
=offset($G$17,0,0,counta($c:$c)-16,1)
or use another offset formula referring to colC. Use the LONGEST column
instead of c:c
then use these ranges in your formula