wild card -- help with formula

[Michael A]

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!

 

[Bob Phillips]

=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]

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]

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)

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

 

[Michael A]

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

 

[Michael A]

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!

 

[Bob Phillips]

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]

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.

 

[Dave Peterson]

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]

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

 

[Bob Phillips]

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)