Counting question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
It didn't work. It totaled 35 (total number of cells) and not the number of
numbers above 0 but below 6.
 
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
 
Careful. I have Q25:V25 in the 1st part of my formula
(?). Change that to:

Q25:W25

Jason
 
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
 
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!
 
I tried this and got an error message.

Domenic said:
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!
Click to expand...
 
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"}))
 
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?
 
Back
Top