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="cxl")*1)

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

Thanks!

=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" <[email protected]> wrote in message
news:[email protected]...
> 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="cxl")*1)
>
> if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="*cxl*")*1) then
it
> wont work.. could anyone please help?
>
> Thanks!

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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="cxl")*1)
> >
> > if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="*cxl*")*1) then
> it
> > wont work.. could anyone please help?
> >
> > Thanks!
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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="cxl")*1)
> > >
> > > if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="*cxl*")*1)
then
> > it
> > > wont work.. could anyone please help?
> > >
> > > Thanks!
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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="cxl")*1)
> > > >
> > > > if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="*cxl*")*1)
> then
> > > it
> > > > wont work.. could anyone please help?
> > > >
> > > > Thanks!
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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="cxl")*1)
> > > > >
> > > > > if I try =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="*cxl*")*1)
> > then
> > > > it
> > > > > wont work.. could anyone please help?
> > > > >
> > > > > Thanks!
> > > >
> > > >
> > > >
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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="cxl")*1)
> > > > > >
> > > > > > if I try
=SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="*cxl*")*1)
> > > then
> > > > > it
> > > > > > wont work.. could anyone please help?
> > > > > >
> > > > > > Thanks!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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="cxl")*1)
> > > > > > >
> > > > > > > if I try
> =SUMPRODUCT((B1:B3000=DATE(2006,1,6))*(H1:H3000="*cxl*")*1)
> > > > then
> > > > > > it
> > > > > > > wont work.. could anyone please help?
> > > > > > >
> > > > > > > Thanks!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
>
>
>

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

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

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