Counting question

[Carl]

I have a range of cells that I want to 'count' if the number is greater than
0 but less than 6. The cell # is F33 where I want the answer. The range is:
Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.

What formula would I use? I've tried several but I keep getting error
answers.

hi,
=count(Q13:W13,Q17:W17,Q21:W17,Q25:W25,Q29:W29)

>-----Original Message-----
>I have a range of cells that I want to 'count' if the
number is greater than
>0 but less than 6. The cell # is F33 where I want the
answer. The range is:
>Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>
>What formula would I use? I've tried several but I keep
getting error
>answers.
>.
>

[Carl]

It didn't work. It totaled 35 (total number of cells) and not the number of
numbers above 0 but below 6.


"[email protected]" wrote:

> hi,
> =count(Q13:W13,Q17:W17,Q21:W17,Q25:W25,Q29:W29)
>
> >-----Original Message-----
> >I have a range of cells that I want to 'count' if the
> number is greater than
> >0 but less than 6. The cell # is F33 where I want the
> answer. The range is:
> >Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> >
> >What formula would I use? I've tried several but I keep
> getting error
> >answers.
> >.
> >
>

[Jason Morin]

Kind of long, but this will work:

=-SUM(-COUNT
(Q13:W13,Q17:W17,Q21:W21,Q25:V25,Q29:W29),COUNTIF(Q13:W13,
{"<=0",">=6"}),COUNTIF(Q17:W17,{"<=0",">=6"}),COUNTIF
(Q21:W21,{"<=0",">=6"}),COUNTIF(Q25:W25,
{"<=0",">=6"}),COUNTIF(Q29:W29,{"<=0",">=6"}))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have a range of cells that I want to 'count' if the
number is greater than
>0 but less than 6. The cell # is F33 where I want the
answer. The range is:
>Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>
>What formula would I use? I've tried several but I keep
getting error
>answers.
>.
>

[Jason Morin]

Careful. I have Q25:V25 in the 1st part of my formula
(?). Change that to:

Q25:W25

Jason

>-----Original Message-----
>Kind of long, but this will work:
>
>=-SUM(-COUNT
>(Q13:W13,Q17:W17,Q21:W21,Q25:V25,Q29:W29),COUNTIF
(Q13:W13,
>{"<=0",">=6"}),COUNTIF(Q17:W17,{"<=0",">=6"}),COUNTIF
>(Q21:W21,{"<=0",">=6"}),COUNTIF(Q25:W25,
>{"<=0",">=6"}),COUNTIF(Q29:W29,{"<=0",">=6"}))
>
>HTH
>Jason
>Atlanta, GA
>
>>-----Original Message-----
>>I have a range of cells that I want to 'count' if the
>number is greater than
>>0 but less than 6. The cell # is F33 where I want the
>answer. The range is:
>>Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>>
>>What formula would I use? I've tried several but I
keep
>getting error
>>answers.
>>.
>>
>.
>

[Jason Morin]

A little shorter than my 1st formula:

=SUM(-COUNTIF(INDIRECT("Q"&{13,17,21,25,29}&":"&"W"&
{13,17,21,25,29}),{"<=0";">=6"}),COUNT
(Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have a range of cells that I want to 'count' if the
number is greater than
>0 but less than 6. The cell # is F33 where I want the
answer. The range is:
>Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>
>What formula would I use? I've tried several but I keep
getting error
>answers.
>.
>

[Carl]

I tried this formula and got an error message.





"Jason Morin" wrote:

> A little shorter than my 1st formula:
>
> =SUM(-COUNTIF(INDIRECT("Q"&{13,17,21,25,29}&":"&"W"&
> {13,17,21,25,29}),{"<=0";">=6"}),COUNT
> (Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29))
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I have a range of cells that I want to 'count' if the
> number is greater than
> >0 but less than 6. The cell # is F33 where I want the
> answer. The range is:
> >Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> >
> >What formula would I use? I've tried several but I keep
> getting error
> >answers.
> >.
> >
>

[Domenic]

Another way...

=SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29
)>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))

Hope this helps!

In article <[email protected]>,
"Carl" <[email protected]> wrote:

> I have a range of cells that I want to 'count' if the number is greater than
> 0 but less than 6. The cell # is F33 where I want the answer. The range is:
> Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
>
> What formula would I use? I've tried several but I keep getting error
> answers.

[Carl]

I tried this and got an error message.

"Domenic" wrote:

> Another way...
>
> =SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29
> )>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))
>
> Hope this helps!
>
> In article <[email protected]>,
> "Carl" <[email protected]> wrote:
>
> > I have a range of cells that I want to 'count' if the number is greater than
> > 0 but less than 6. The cell # is F33 where I want the answer. The range is:
> > Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> >
> > What formula would I use? I've tried several but I keep getting error
> > answers.
>

[Domenic]

Can you post the exact formula you're using?

In article <[email protected]>,
"Carl" <[email protected]> wrote:

> I tried this and got an error message.
>
> "Domenic" wrote:
>
> > Another way...
> >
> > =SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29
> > )>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))
> >
> > Hope this helps!
> >
> > In article <[email protected]>,
> > "Carl" <[email protected]> wrote:
> >
> > > I have a range of cells that I want to 'count' if the number is greater
> > > than
> > > 0 but less than 6. The cell # is F33 where I want the answer. The range
> > > is:
> > > Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> > >
> > > What formula would I use? I've tried several but I keep getting error
> > > answers.
> >

[Carl]

I tried both formulas that Jason gave me and the one you gave me.

=-SUM(-COUNT
(Q13:W13,Q17:W17,Q21:W21,Q25:V25,Q29:W29),COUNTIF(Q13:W13,
{"<=0",">=6"}),COUNTIF(Q17:W17,{"<=0",">=6"}),COUNTIF
(Q21:W21,{"<=0",">=6"}),COUNTIF(Q25:W25,
{"<=0",">=6"}),COUNTIF(Q29:W29,{"<=0",">=6"}))


"Domenic" wrote:

> Can you post the exact formula you're using?
>
> In article <[email protected]>,
> "Carl" <[email protected]> wrote:
>
> > I tried this and got an error message.
> >
> > "Domenic" wrote:
> >
> > > Another way...
> > >
> > > =SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29
> > > )>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))
> > >
> > > Hope this helps!
> > >
> > > In article <[email protected]>,
> > > "Carl" <[email protected]> wrote:
> > >
> > > > I have a range of cells that I want to 'count' if the number is greater
> > > > than
> > > > 0 but less than 6. The cell # is F33 where I want the answer. The range
> > > > is:
> > > > Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> > > >
> > > > What formula would I use? I've tried several but I keep getting error
> > > > answers.
> > >
>

[Domenic]

If you copied and pasted the formula from the newsgroup and into your
spreadsheet, extra spaces and hard returns may have been added/included
in the formula. If so, this would give you your error message. In this
case, correct the formula accordingly.

Does this help?

In article <[email protected]>,
"Carl" <[email protected]> wrote:

> I tried both formulas that Jason gave me and the one you gave me.
>
> =-SUM(-COUNT
> (Q13:W13,Q17:W17,Q21:W21,Q25:V25,Q29:W29),COUNTIF(Q13:W13,
> {"<=0",">=6"}),COUNTIF(Q17:W17,{"<=0",">=6"}),COUNTIF
> (Q21:W21,{"<=0",">=6"}),COUNTIF(Q25:W25,
> {"<=0",">=6"}),COUNTIF(Q29:W29,{"<=0",">=6"}))
>
>
> "Domenic" wrote:
>
> > Can you post the exact formula you're using?
> >
> > In article <[email protected]>,
> > "Carl" <[email protected]> wrote:
> >
> > > I tried this and got an error message.
> > >
> > > "Domenic" wrote:
> > >
> > > > Another way...
> > > >
> > > > =SUMPRODUCT(--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W2
> > > > 9
> > > > )>0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W25,Q29:W29)<6))
> > > >
> > > > Hope this helps!
> > > >
> > > > In article <[email protected]>,
> > > > "Carl" <[email protected]> wrote:
> > > >
> > > > > I have a range of cells that I want to 'count' if the number is
> > > > > greater
> > > > > than
> > > > > 0 but less than 6. The cell # is F33 where I want the answer. The
> > > > > range
> > > > > is:
> > > > > Q13:W13; Q17:W17; Q21:W17; Q25:W25; & Q29:W29.
> > > > >
> > > > > What formula would I use? I've tried several but I keep getting
> > > > > error
> > > > > answers.
> > > >
> >