| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Wild card * | Excel Worksheet Functions | |||
| adding row to forumla | Excel Discussion (Misc queries) | |||
| Creating a check mark box | Setting up and Configuration of Excel | |||
Hybrid Mode
