 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2006Help With This Formula ....
HELLO, For some reason I keep getting a "TOO FEW ARGUMENTS" error with this formula: =COUNTIF(OR('DAY1'!E6:E35<>"",LEFT('DAY1'!G6:G35,2)<>"GI")) I am simply trying to use a count if 2 conditions are met... 1. if Fields E6:E35 is not empty and 2. Fields G6:G35 Left 2 Characters <> "GI" then count. But The formula is not being accepted as I have it above... Any help here is much appreciated... Thanks In Advance  SignatureMhz > HELLO, > [quoted text clipped - 16 lines] > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=564648 Hi, Maybe you can use SUMPRODUCT(). Something along the lines of: =SUMPRODUCT(--(E6:E35<>0),--(LEFT(G6:G35,2)<>"GI")) Regards, Bondi That's an AND not an OR =SUMPRODUCT(--(((DAY1!E6:E35<>"")+(LEFT(DAY1!G6:G35,2)<>"GI"))>0)) SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > > HELLO, > > [quoted text clipped - 25 lines] > Regards, > Bondi Thanks alot ,, :) Actually the "=SUMPRODUCT(--(E6:E35<>0),--(LEFT(G6:G35,2)<>"GI"))" Formula worked well, but I had to modify it to read the DAY1.... The =SUMPRODUCT(--(((DAY1!E6:E35<>"")+(LEFT(DAY1!G6:G35,2)<>"GI"))>0 )) also works, but it appears to accumulate the blank cells as well. Anyhow thanks for the fast responses... by the way, what do the -- (double negatives) represent ? SignatureMhz It wpuld accumulate blanks, because they do not equal GI which was part of your criteria. See http://xldynamic.com/source/xld.SUMPRODUCT.html for the explanation of --. > Thanks alot ,, :) > [quoted text clipped - 13 lines] > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=564648 Hello, First, it sounds like you want to use the AND operator not OR - you want BOTH conditions to be met. Second, you didn't specify anything to count - you set the conditions. If E6:E35 is not blank and G6:G35 doesn't = "GI" then count...count what? There should be a range before your criteria. See example below: =COUNTIF(A:A, AND('DAY1'!E6:E35<>"",LEFT('DAY1'!G6:G35,2)<>"GI")) > HELLO, > [quoted text clipped - 16 lines] > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > View this thread: http://www.excelforum.com/showthread.php?threadid=564648 Have you actually tried that Chris? COUNTIF will not work like that. It won't test two values, and it tests the first range for the condition, what is A:A all about? SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Hello, > [quoted text clipped - 26 lines] > > Mhz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35980 > > View this thread: http://www.excelforum.com/showthread.php?threadid=564648 Thanks Bob for the accumalation response regarding blank cells... Yes, you're correct, The formula Chris provided doesn't seem to get an accumalative value.. I also wanted to know the A:A purpose. By the way Chris, I was trying to count the number of occurances (Sum) of any factors that met the conditions being tested. thanks for the help anyhow..:) SignatureMhz Do you need a variation of the formula that will not accumulate if both blank? SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Thanks Bob for the accumalation response regarding blank cells... > [quoted text clipped - 4 lines] > of any factors that met the conditions being tested. thanks for the > help anyhow..:) Yes Bob, The Idea is to create a formula that would check if there is a Phone Number (E6:E35) First, and if that is true then Check for the Left Conditions (G6:G35) if that condition is true then Count or (Sum Values) .. But a Blank in E Cells (No Phone Number), then NO Count or (Summing of Values).. Thanks In Advance.. I also appear to have a problem when I tried to use Not Equal "<>" commands the returns will not work properly. Is this because of the "--" or the formula script itself... eg. This Returns Good Values: =SUMPRODUCT(--('DAY1'!E6:E35<>0),--(LEFT('DAY1'!G6:G35,2)=""))+SUMPRODUCT(--(LEFT('DAY1'!G6:G35,1)="/")) Phone Number Cells Not Blank, G Cells = Blank, Left Character G Cell = "/" These Conditions works well to return a count.. ------------------------------------------------------------- This Returns bad Values: (Bad meaning incorrect for the conditions tested) =SUMPRODUCT(--('DAY1'!E6:E35<>0),--(LEFT('DAY1'!G6:G35,2)<>"GI"))+SUMPRODUCT(--(LEFT('DAY1'!G6:G35,1)<>"M")) Phone Number Cell Not Blank, Left Function of G Cells <> "GI" or "M" This Formula Doesn't Return accurate Results. thanks for checking this out... SignatureMhz Try this =SUMPRODUCT(--(DAY1!E6:E35<>0),--(LEFT(DAY1!G6:G35,2)<>"GI"),--(LEFT(DAY1!G6 :G35,1)<>"M")) the problem with your test was that it tested for E <> "" and G <> GI, but did a separate test for G <> "M" and added them, you need it all in one test SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Yes Bob, The Idea is to create a formula that would check if there is a > Phone Number (E6:E35) First, and if that is true then Check for the [quoted text clipped - 11 lines] > > This Returns Good Values: =SUMPRODUCT(--('DAY1'!E6:E35<>0),--(LEFT('DAY1'!G6:G35,2)=""))+SUMPRODUCT(-- (LEFT('DAY1'!G6:G35,1)="/")) > Phone Number Cells Not Blank, G Cells = Blank, Left Character G Cell = > "/" These Conditions works well to return a count.. > ------------------------------------------------------------- > This Returns bad Values: (Bad meaning incorrect for the conditions > tested) =SUMPRODUCT(--('DAY1'!E6:E35<>0),--(LEFT('DAY1'!G6:G35,2)<>"GI"))+SUMPRODUCT (--(LEFT('DAY1'!G6:G35,1)<>"M")) > Phone Number Cell Not Blank, Left Function of G Cells <> "GI" or "M" > This Formula Doesn't Return accurate Results. > > thanks for checking this out... That was Exactly the Problem Bob! Your Formula Works Flawless! Now I see what I was doing wrong... Thanks Very Much :) SignatureMhz |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.