need formula to add two different criteria in a range
 

I want to count the number of "yes" in a range and the number of "n/a" in a
range and get that total.
If I enter =sum(if(range,"yes"+"n/a")) I get "#Value"
If I enter =sum(if(range,"yes")+(range,"n/a")) I get "formula contains an
error"
I've tried several variations on this with sum, sumif, count, counta,
countif...
Can someone please give me a clue?? :D
Thanks, Meenie
--
Y****er

need formula to add two different criteria in a range
 

=COUNTIF(A1:A100,"yes")
if n/a was entered as text
=COUNTIF(A1:A100,"n/a")
if n/a was enterd with the NA() function
=SUMPRODUCT(--(ISNA(A1:A100)))

best wishes
--
www.stfx.ca/people/bliengme


"Meenie" wrote:

I want to count the number of "yes" in a range and the number of "n/a" in a
range and get that total.
If I enter =sum(if(range,"yes"+"n/a")) I get "#Value"
If I enter =sum(if(range,"yes")+(range,"n/a")) I get "formula contains an
error"
I've tried several variations on this with sum, sumif, count, counta,
countif...
Can someone please give me a clue?? :D
Thanks, Meenie
--
Y****er

need formula to add two different criteria in a range
 

Hi Meenie

Try
=SUM(COUNTIF(A:A,{"Yes","n/a"}))

--
Jacob (MVP - Excel)


"Meenie" wrote:

I want to count the number of "yes" in a range and the number of "n/a" in a
range and get that total.
If I enter =sum(if(range,"yes"+"n/a")) I get "#Value"
If I enter =sum(if(range,"yes")+(range,"n/a")) I get "formula contains an
error"
I've tried several variations on this with sum, sumif, count, counta,
countif...
Can someone please give me a clue?? :D
Thanks, Meenie
--
Y****er

need formula to add two different criteria in a range
 

yes and n/a are both entered as text.
I know how to count one or the other, how to I count the total occurence of
both within the range?
--
Y****er


"Bernard Liengme" wrote:

=COUNTIF(A1:A100,"yes")
if n/a was entered as text
=COUNTIF(A1:A100,"n/a")
if n/a was enterd with the NA() function
=SUMPRODUCT(--(ISNA(A1:A100)))

best wishes
--
www.stfx.ca/people/bliengme


"Meenie" wrote:

I want to count the number of "yes" in a range and the number of "n/a" in a
range and get that total.
If I enter =sum(if(range,"yes"+"n/a")) I get "#Value"
If I enter =sum(if(range,"yes")+(range,"n/a")) I get "formula contains an
error"
I've tried several variations on this with sum, sumif, count, counta,
countif...
Can someone please give me a clue?? :D
Thanks, Meenie
--
Y****er

need formula to add two different criteria in a range
 

Perfect!! Thanks Jacob :)
--
Y****er


"Jacob Skaria" wrote:

Hi Meenie

Try
=SUM(COUNTIF(A:A,{"Yes","n/a"}))

--
Jacob (MVP - Excel)


"Meenie" wrote:

I want to count the number of "yes" in a range and the number of "n/a" in a
range and get that total.
If I enter =sum(if(range,"yes"+"n/a")) I get "#Value"
If I enter =sum(if(range,"yes")+(range,"n/a")) I get "formula contains an
error"
I've tried several variations on this with sum, sumif, count, counta,
countif...
Can someone please give me a clue?? :D
Thanks, Meenie
--
Y****er

need formula to add two different criteria in a range
 

Add the two functions together...
=COUNTIF(A1:A100,"yes")+COUNTIF(A1:A100,"n/a")

--
Best Regards,

Luke M
"Meenie" wrote in message
...
yes and n/a are both entered as text.
I know how to count one or the other, how to I count the total occurence
of
both within the range?
--
Y****er


"Bernard Liengme" wrote:

=COUNTIF(A1:A100,"yes")
if n/a was entered as text
=COUNTIF(A1:A100,"n/a")
if n/a was enterd with the NA() function
=SUMPRODUCT(--(ISNA(A1:A100)))

best wishes
--
www.stfx.ca/people/bliengme


"Meenie" wrote:

I want to count the number of "yes" in a range and the number of "n/a"
in a
range and get that total.
If I enter =sum(if(range,"yes"+"n/a")) I get "#Value"
If I enter =sum(if(range,"yes")+(range,"n/a")) I get "formula contains
an
error"
I've tried several variations on this with sum, sumif, count, counta,
countif...
Can someone please give me a clue?? :D
Thanks, Meenie
--
Y****er

need formula to add two different criteria in a range
 

This will work for those =na() errors:
=countif(a1:a100,"#n/a")

Bernard Liengme wrote:

=COUNTIF(A1:A100,"yes")
if n/a was entered as text
=COUNTIF(A1:A100,"n/a")
if n/a was enterd with the NA() function
=SUMPRODUCT(--(ISNA(A1:A100)))

best wishes
--
www.stfx.ca/people/bliengme

"Meenie" wrote:

I want to count the number of "yes" in a range and the number of "n/a" in a
range and get that total.
If I enter =sum(if(range,"yes"+"n/a")) I get "#Value"
If I enter =sum(if(range,"yes")+(range,"n/a")) I get "formula contains an
error"
I've tried several variations on this with sum, sumif, count, counta,
countif...
Can someone please give me a clue?? :D
Thanks, Meenie
--
Y****er

--

Dave Peterson