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]

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

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)

 

[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]

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

 

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

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]

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]

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)

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:

 

[Mhz]

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