Wildcard not working in SumProduct Array
 

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!!

Wildcard not working in SumProduct Array
 

More info on your layout

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roady" wrote in message
...
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!!

Wildcard not working in SumProduct Array
 

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!!

Wildcard not working in SumProduct Array
 

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!

"Don Guillett" wrote:

More info on your layout

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roady" wrote in message
...
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!!

.

Wildcard not working in SumProduct Array
 

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!!

Wildcard not working in SumProduct Array
 

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

"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!!

Wildcard not working in SumProduct Array
 

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.


"Roady" wrote:

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!!

Wildcard not working in SumProduct Array
 

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Mike H" wrote:

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.


"Roady" wrote:

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!!

Wildcard not working in SumProduct Array
 

I would suggest using self adjusting defined name ranges instead so you only
calculate what is necessary.
While on the sheet with the rangesinsertnamedefinename colCin 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



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roady" wrote in message
...
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!!