wild card -- help with formula
 

Hello all. I cant seem to get this formula to work right when I use a wild
card. I need to count how many entries in column H: have "CXL" in the text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1) then it
wont work.. could anyone please help?

Thanks!

wild card -- help with formula
 

=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I use a wild
card. I need to count how many entries in column H: have "CXL" in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1) then
it
wont work.. could anyone please help?

Thanks!

wild card -- help with formula
 

Hi Bob,

Thank you for the response. Do you think that I could do this with VB
script? the value "nstuff" is from a userinput box asking for the date.


j = Application.SumProduct(--(.Columns(2) = nStuff), --(IsNumber(Find("cxl",
..Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:


=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I use a wild
card. I need to count how many entries in column H: have "CXL" in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1) then
it
wont work.. could anyone please help?

Thanks!

wild card -- help with formula
 

That is because ISNUMBER and FIND are also worksheet functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and Application.FIND.

Bt even then, SP won't resolve using that technique. I always use Evaluate
in VBA

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

Thank you for the response. Do you think that I could do this with VB
script? the value "nstuff" is from a userinput box asking for the date.


j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:



=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I use a
wild
card. I need to count how many entries in column H: have "CXL" in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?

Thanks!

wild card -- help with formula
 

Thank you for the response Bob. Sorry about the other threads. I thought
since it was a "different question" that it would belong in a different
thread for other people to search for. And then again, under the
"programming" category.

I'll try this. Thanks for your help.

- Mike

"Bob Phillips" wrote:

That is because ISNUMBER and FIND are also worksheet functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and Application.FIND.

Bt even then, SP won't resolve using that technique. I always use Evaluate
in VBA

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

Thank you for the response. Do you think that I could do this with VB
script? the value "nstuff" is from a userinput box asking for the date.


j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:



=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I use a
wild
card. I need to count how many entries in column H: have "CXL" in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?

Thanks!

wild card -- help with formula
 

Hi Bob,

I am getting a "Type Mismatch" error on that line. I'm not sure what even
causes that... Is there anything else I need to do with this line? Thanks!

Michael A" wrote:

Thank you for the response Bob. Sorry about the other threads. I thought
since it was a "different question" that it would belong in a different
thread for other people to search for. And then again, under the
"programming" category.

I'll try this. Thanks for your help.

- Mike

"Bob Phillips" wrote:

That is because ISNUMBER and FIND are also worksheet functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and Application.FIND.

Bt even then, SP won't resolve using that technique. I always use Evaluate
in VBA

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

Thank you for the response. Do you think that I could do this with VB
script? the value "nstuff" is from a userinput box asking for the date.


j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:



=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I use a
wild
card. I need to count how many entries in column H: have "CXL" in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?

Thanks!

wild card -- help with formula
 

Probably NG wrap-around. Try

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
CLng(nStuff) & "),--(IsNumber(Find(""cxl"",H1:H3000))))")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

I am getting a "Type Mismatch" error on that line. I'm not sure what even
causes that... Is there anything else I need to do with this line? Thanks!

Michael A" wrote:

Thank you for the response Bob. Sorry about the other threads. I thought
since it was a "different question" that it would belong in a different
thread for other people to search for. And then again, under the
"programming" category.

I'll try this. Thanks for your help.

- Mike

"Bob Phillips" wrote:

That is because ISNUMBER and FIND are also worksheet functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and
Application.FIND.

Bt even then, SP won't resolve using that technique. I always use
Evaluate
in VBA

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

Thank you for the response. Do you think that I could do this with
VB
script? the value "nstuff" is from a userinput box asking for the
date.


j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:




=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I
use a
wild
card. I need to count how many entries in column H: have "CXL"
in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try
=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?

Thanks!

wild card -- help with formula
 

Hi Bob,

Still getting the same error. I will play with it tomorrow more and see what
I can figure out. Still the "Type Mismatch Error". Thanks again for being so
generous with your time.



"Bob Phillips" wrote:

Probably NG wrap-around. Try

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
CLng(nStuff) & "),--(IsNumber(Find(""cxl"",H1:H3000))))")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

I am getting a "Type Mismatch" error on that line. I'm not sure what even
causes that... Is there anything else I need to do with this line? Thanks!

Michael A" wrote:

Thank you for the response Bob. Sorry about the other threads. I thought
since it was a "different question" that it would belong in a different
thread for other people to search for. And then again, under the
"programming" category.

I'll try this. Thanks for your help.

- Mike

"Bob Phillips" wrote:

That is because ISNUMBER and FIND are also worksheet functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and
Application.FIND.

Bt even then, SP won't resolve using that technique. I always use
Evaluate
in VBA

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

Thank you for the response. Do you think that I could do this with
VB
script? the value "nstuff" is from a userinput box asking for the
date.


j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:




=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I
use a
wild
card. I need to count how many entries in column H: have "CXL"
in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try
=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?

Thanks!

wild card -- help with formula
 

You may want to test nStuff to see if it's really a date, too.

And maybe use this CLng(CDate(nStuff)) in the sumproduct formula.
if you're using
nstuff = inputbox("enter a date")

Another option:

Dim j As Long
Dim nStuff As Long

nStuff = Application.InputBox("date", Type:=1)

'add your own validation
If Year(nStuff) < 2000 _
Or Year(nStuff) 2010 Then
'it's not a valid date?????
Exit Sub
End If

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
nStuff & "),--(IsNumber(Find(""cxl"",H1:H3000))))")



Michael A wrote:

Hi Bob,

Still getting the same error. I will play with it tomorrow more and see what
I can figure out. Still the "Type Mismatch Error". Thanks again for being so
generous with your time.

"Bob Phillips" wrote:

Probably NG wrap-around. Try

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
CLng(nStuff) & "),--(IsNumber(Find(""cxl"",H1:H3000))))")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

I am getting a "Type Mismatch" error on that line. I'm not sure what even
causes that... Is there anything else I need to do with this line? Thanks!

Michael A" wrote:

Thank you for the response Bob. Sorry about the other threads. I thought
since it was a "different question" that it would belong in a different
thread for other people to search for. And then again, under the
"programming" category.

I'll try this. Thanks for your help.

- Mike

"Bob Phillips" wrote:

That is because ISNUMBER and FIND are also worksheet functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and
Application.FIND.

Bt even then, SP won't resolve using that technique. I always use
Evaluate
in VBA

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

Thank you for the response. Do you think that I could do this with
VB
script? the value "nstuff" is from a userinput box asking for the
date.


j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:




=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I
use a
wild
card. I need to count how many entries in column H: have "CXL"
in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try
=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?

Thanks!










--

Dave Peterson

wild card -- help with formula
 

Hi Dave,

Thanks for the response.The CLng(CDate(nStuff)) seems to work better. It
dosn't return an error, and it acutally counts how many entries there are if
it is an exact match. However its not quite working as I am looking for. I'm
afraid this has gone far beyond what i know! :|

If the column has just "CXL" in it, then it adds it up. However if it has
"CXL12#etc" then it wont add it up. I need it to add up the line if CXL is in
any part of the cell. Any help would be great.

Thanks!
Mike
"Dave Peterson" wrote:

You may want to test nStuff to see if it's really a date, too.

And maybe use this CLng(CDate(nStuff)) in the sumproduct formula.
if you're using
nstuff = inputbox("enter a date")

Another option:

Dim j As Long
Dim nStuff As Long

nStuff = Application.InputBox("date", Type:=1)

'add your own validation
If Year(nStuff) < 2000 _
Or Year(nStuff) 2010 Then
'it's not a valid date?????
Exit Sub
End If

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
nStuff & "),--(IsNumber(Find(""cxl"",H1:H3000))))")



Michael A wrote:

Hi Bob,

Still getting the same error. I will play with it tomorrow more and see what
I can figure out. Still the "Type Mismatch Error". Thanks again for being so
generous with your time.

"Bob Phillips" wrote:

Probably NG wrap-around. Try

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
CLng(nStuff) & "),--(IsNumber(Find(""cxl"",H1:H3000))))")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

I am getting a "Type Mismatch" error on that line. I'm not sure what even
causes that... Is there anything else I need to do with this line? Thanks!

Michael A" wrote:

Thank you for the response Bob. Sorry about the other threads. I thought
since it was a "different question" that it would belong in a different
thread for other people to search for. And then again, under the
"programming" category.

I'll try this. Thanks for your help.

- Mike

"Bob Phillips" wrote:

That is because ISNUMBER and FIND are also worksheet functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and
Application.FIND.

Bt even then, SP won't resolve using that technique. I always use
Evaluate
in VBA

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

Thank you for the response. Do you think that I could do this with
VB
script? the value "nstuff" is from a userinput box asking for the
date.


j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:




=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hello all. I cant seem to get this formula to work right when I
use a
wild
card. I need to count how many entries in column H: have "CXL"
in the
text.

here is what I have

=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try
=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?

Thanks!










--

Dave Peterson

wild card -- help with formula
 

Michael,

Send me your workbook to look at. My addy is

bob dot phillips at tiscali dot co dot uk

Do the obvious.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Dave,

Thanks for the response.The CLng(CDate(nStuff)) seems to work better. It
dosn't return an error, and it acutally counts how many entries there are
if
it is an exact match. However its not quite working as I am looking for.
I'm
afraid this has gone far beyond what i know! :|

If the column has just "CXL" in it, then it adds it up. However if it has
"CXL12#etc" then it wont add it up. I need it to add up the line if CXL is
in
any part of the cell. Any help would be great.

Thanks!
Mike
"Dave Peterson" wrote:

You may want to test nStuff to see if it's really a date, too.

And maybe use this CLng(CDate(nStuff)) in the sumproduct formula.
if you're using
nstuff = inputbox("enter a date")

Another option:

Dim j As Long
Dim nStuff As Long

nStuff = Application.InputBox("date", Type:=1)

'add your own validation
If Year(nStuff) < 2000 _
Or Year(nStuff) 2010 Then
'it's not a valid date?????
Exit Sub
End If

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
nStuff & "),--(IsNumber(Find(""cxl"",H1:H3000))))")



Michael A wrote:

Hi Bob,

Still getting the same error. I will play with it tomorrow more and
see what
I can figure out. Still the "Type Mismatch Error". Thanks again for
being so
generous with your time.

"Bob Phillips" wrote:

Probably NG wrap-around. Try

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" & _
CLng(nStuff) & "),--(IsNumber(Find(""cxl"",H1:H3000))))")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in message
...
Hi Bob,

I am getting a "Type Mismatch" error on that line. I'm not sure
what even
causes that... Is there anything else I need to do with this line?
Thanks!

Michael A" wrote:

Thank you for the response Bob. Sorry about the other threads. I
thought
since it was a "different question" that it would belong in a
different
thread for other people to search for. And then again, under the
"programming" category.

I'll try this. Thanks for your help.

- Mike

"Bob Phillips" wrote:

That is because ISNUMBER and FIND are also worksheet
functions, as is
SUMPRODUCT, so you would need Application.ISNUMBER and
Application.FIND.

Bt even then, SP won't resolve using that technique. I always
use
Evaluate
in VBA

j = ActiveSheet.Evaluate("=SumProduct(--(B1:B3000=" &
CLng(nStuff) &
"),--(IsNumber(Find(""cxl"",H1:H3000))))")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in
message
...
Hi Bob,

Thank you for the response. Do you think that I could do
this with
VB
script? the value "nstuff" is from a userinput box asking
for the
date.


j = Application.SumProduct(--(.Columns(2) =
nStuff), --(IsNumber(Find("cxl",
.Columns(8)))))

It dosn't seem to know what "FIND" is. Any suggestions?

"Bob Phillips" wrote:





=SUMPRODUCT(--(B1:B3000=DATE(2006,1,6)),--(ISNUMBER(FIND("cxl",H1:H3000))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Michael A" wrote in
message
...
Hello all. I cant seem to get this formula to work right
when I
use a
wild
card. I need to count how many entries in column H: have
"CXL"
in the
text.

here is what I have


=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="c xl")*1)

if I try
=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="* cxl*")*1)
then
it
wont work.. could anyone please help?

Thanks!










--

Dave Peterson