Prev: sumif formula
Next: When I search on my name "Meenie" I don't get 2010 posts
From: Meenie on 29 Apr 2010 10:09
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: Bernard Liengme on 29 Apr 2010 10:17
=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: Jacob Skaria on 29 Apr 2010 10:21
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
> --
> Ytwater
From: Meenie on 29 Apr 2010 10:23
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: Meenie on 29 Apr 2010 10:31
Perfect!! Thanks Jacob :)
--
Ytwater


"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
> > --
> > Ytwater
 |  Next  |  Last
Pages: 1 2
Prev: sumif formula
Next: When I search on my name "Meenie" I don't get 2010 posts