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!