ExcelBanter - Counting question

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Counting question (https://www.excelbanter.com/excel-discussion-misc-queries/15962-counting-question.html)

Carl

Counting question

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.

" 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"}),COUN TIF
(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),COUNT IF
(Q13:W13,
{"<=0","=6"}),COUNTIF(Q17:W17,{"<=0","=6"}),COU NTIF
(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.
.

Domenic

Another way...

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

Hope this helps!

In article ,
"Carl" 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 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.
.

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:W2 5,Q29:W29
)0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W2 5,Q29:W29)<6))

Hope this helps!

In article ,
"Carl" 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 ,
"Carl" 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:W2 5,Q29:W29
)0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W2 5,Q29:W29)<6))

Hope this helps!

In article ,
"Carl" 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"}),COUN TIF
(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 ,
"Carl" 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:W2 5,Q29:W29
)0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W2 5,Q29:W29)<6))

Hope this helps!

In article ,
"Carl" 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 ,
"Carl" 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"}),COUN TIF
(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 ,
"Carl" 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:W2 5,Q29:W2
9
)0),--(CHOOSE({1;2;3;4;5},Q13:W13,Q17:W17,Q21:W21,Q25:W2 5,Q29:W29)<6))

Hope this helps!

In article ,
"Carl" 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.

All times are GMT +1. The time now is 07:28 AM.