COUNTIF function with 2 conditions

  • Thread starter Thread starter xadamz23
  • Start date Start date
X

xadamz23

Is there a way to use the COUNTIF function with 2 conditions?

Right now I have the function:


*COUNTIF(F11:F502,"Amisys")* which works fine.

I want something like this:

*COUNTIF(F11:F502,"Amisys"&E11<>"No Call Back")*

But that doesnt work.

Anyone have any ideas?
 
Thanks for the quick response Frank, but it didnt work correctly.

What I want is the function to only count the cells when Fwhatever =
Amisys *AND* Ewhatever <> No Call Back



Any more ideas?

Thanks
 
Try this:

=SUMPRODUCT((F11:F502="Amysis")*(E11<>"no call back"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Is there a way to use the COUNTIF function with 2 conditions?

Right now I have the function:


*COUNTIF(F11:F502,"Amisys")* which works fine.

I want something like this:

*COUNTIF(F11:F502,"Amisys"&E11<>"No Call Back")*

But that doesnt work.

Anyone have any ideas?
 
Hi
but this is what the formula does return. What is the exact error you
get. (maybe post some example rows in plain text)
 
Here are some of the rows:


A B C D
E F
11 1/1/2004 Terri Usry 2:30 PM 2:35 PM 0:05:00 Amisys
12 1/1/2004 Shawn David 9:35 PM 9:40 PM 0:05:00 Networking
13 1/2/2004 John Lee 11:18 PM 11:26 PM 0:08:00 Networking
 
I have the formula check the cell for that text. If it isnt there the
I want the cell included in the count. If the text "No call back" i
there, I DONT want it included.





Frank said:
*Hi
and how do you know the condition 'No call back'?

--
Regards
Frank Kabel
Frankfurt, Germany

xadamz23 > said:
Here are some of the rows:


A B C D
E F
11 1/1/2004 Terri Usry 2:30 PM 2:35 PM 0:05:00 Amisys
12 1/1/2004 Shawn David 9:35 PM 9:40 PM 0:05:00 Networking
13 1/2/2004 John Lee 11:18 PM 11:26 PM 0:08:00 Networking





Click to expand...
Click to expand...
 
Thanks for the responce RagDyer. I get a divide by zero error with tha
formula.

My entire formula is:

*SUMIF(F11:F502,"Amisys",E11:E502)/COUNTIF(F11:F502,"Amisys")*
 
Hi
was just asking as your example didn't show any such rows. But then my
formula should work. Could you give an example where it doesn't work
and what you get as (wrong) result

--
Regards
Frank Kabel
Frankfurt, Germany

xadamz23 > said:
I have the formula check the cell for that text. If it isnt there then
I want the cell included in the count. If the text "No call back" is
there, I DONT want it included.





Frank said:
*Hi
and how do you know the condition 'No call back'?
Click to expand...
Click to expand...
 
Hi
this would indicate that where is no matching entry in column F for
"Amisys". You may check for leading/trainling spaces
 
Hey Frank,

Ive attached the excel file for you to look at.

Thanks again.


Frank said:
*Hi
was just asking as your example didn't show any such rows. But the
my
formula should work. Could you give an example where it doesn't work
and what you get as (wrong) result

--
Regards
Frank Kabel
Frankfurt, Germany

xadamz23 > said:
I have the formula check the cell for that text. If it isnt there then
I want the cell included in the count. If the text "No call back is
there, I DONT want it included.
Click to expand...

Attachment filename: 2004.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55160
Click to expand...
 
Did you notice that I misspelled Amisys?
Plus, I *literally* followed your OP, and used the *single* E11 cell!

Try this formula:

=SUMIF(F11:F502,"Amisys",E11:E502)/SUMPRODUCT((F11:F502="Amisys")*(E11:E502<
"no call back"))
Click to expand...
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thanks for the responce RagDyer. I get a divide by zero error with that
formula.

My entire formula is:

*SUMIF(F11:F502,"Amisys",E11:E502)/COUNTIF(F11:F502,"Amisys")*
 
Thanks,

That worked.

Now, is there a way I can count only the rows that say Amisys AND N
call back?
 
Just change "not equal to" (<>)
To "equal to" (=)

=SUMPRODUCT((F11:F502="Amisys")*(E11:E502="no call back"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Thanks,

That worked.

Now, is there a way I can count only the rows that say Amisys AND No
call back?
 
Hi
no file attached. In addition: NEVER attach a file to this newsgroup.
You can email me your file:
email: frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

xadamz23 > said:
Hey Frank,

Ive attached the excel file for you to look at.

Thanks again.


Frank said:
*Hi
was just asking as your example didn't show any such rows. But then
my
formula should work. Could you give an example where it doesn't work
and what you get as (wrong) result

--
Regards
Frank Kabel
Frankfurt, Germany

xadamz23 > said:
I have the formula check the cell for that text. If it isnt
Click to expand...
there
then
I want the cell included in the count. If the text "No call
Click to expand...
back"
is
Click to expand...
Click to expand...
 
Change your formula to:


=SUMIF(F11:F502,"Amisys",E11:E502)/SUMPRODUCT(--(F11:F502="Amisys"),--(D11:D502<>"No
Call Back"))

and format the cell as a number, instead of time.
Hey Frank,

Ive attached the excel file for you to look at.

Thanks again.


Frank said:
*Hi
was just asking as your example didn't show any such rows. But then
my
formula should work. Could you give an example where it doesn't work
and what you get as (wrong) result

--
Regards
Frank Kabel
Frankfurt, Germany

I have the formula check the cell for that text. If it isnt there
then

I want the cell included in the count. If the text "No call back"
is

there, I DONT want it included.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top