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?

"Mike H" wrote:

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),--(D17:D65535="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" wrote:

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),--(D17:D65535="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!!