Help With This Formula ....

[Mhz]

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

[Bondi]

Mhz wrote:
> 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
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=564648

Hi,

Maybe you can use SUMPRODUCT(). Something along the lines of:

=SUMPRODUCT(--(E6:E35<>0),--(LEFT(G6:G35,2)<>"GI"))

Regards,
Bondi

[Bob Phillips]

That's an AND not an OR

=SUMPRODUCT(--(((DAY1!E6:E35<>"")+(LEFT(DAY1!G6:G35,2)<>"GI"))>0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bondi" <[email protected]> wrote in message
news:[email protected]...
>
> Mhz wrote:
> > 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
> >
> >
> > --
> > Mhz
> > ------------------------------------------------------------------------
> > Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
> > View this thread:
http://www.excelforum.com/showthread...hreadid=564648
>
> Hi,
>
> Maybe you can use SUMPRODUCT(). Something along the lines of:
>
> =SUMPRODUCT(--(E6:E35<>0),--(LEFT(G6:G35,2)<>"GI"))
>
> Regards,
> Bondi
>

[Chris]

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

Mhz wrote:
> 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
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=564648

[Mhz]

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 ?

[Bob Phillips]

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 --.


"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> 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 ?
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=564648
>

[Bob Phillips]

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?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Chris" <[email protected]> wrote in message
news:[email protected]...
> 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"))
>
> Mhz wrote:
> > 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
> >
> >
> > --
> > Mhz
> > ------------------------------------------------------------------------
> > Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
> > View this thread:
http://www.excelforum.com/showthread...hreadid=564648
>

[Mhz]

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..

[Bob Phillips]

Do you need a variation of the formula that will not accumulate if both
blank?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> 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..
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=564648
>

[Mhz]

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...

[Bob Phillips]

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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Mhz" <[email protected]> wrote in message
news:[email protected]...
>
> 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...
>
>
> --
> Mhz
> ------------------------------------------------------------------------
> Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
> View this thread: http://www.excelforum.com/showthread...hreadid=564648
>

[Mhz]

That was Exactly the Problem Bob! Your Formula Works Flawless! Now I see what I was doing wrong... Thanks Very Much