Prev: sumif formula
Next: When I search on my name "Meenie" I don't get 2010 posts
From: Luke M on 29 Apr 2010 10:55
Add the two functions together...
=COUNTIF(A1:A100,"yes")+COUNTIF(A1:A100,"n/a")

--
Best Regards,

Luke M
"Meenie" <Meenie(a)discussions.microsoft.com> wrote in message
news:F51EFBEF-1CAE-45D5-A5B8-659A2523937F(a)microsoft.com...
> 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?
> --
> Ytwater
>
>
> "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
>> > --
>> > Ytwater


From: Dave Peterson on 29 Apr 2010 18:15
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
> > --
> > Ytwater

--

Dave Peterson
First  |  Prev  | 
Pages: 1 2
Prev: sumif formula
Next: When I search on my name "Meenie" I don't get 2010 posts