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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
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 |