| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
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. . |
|
#3
|
|||
|
|||
|
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. . |
|
#4
|
|||
|
|||
|
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. . |
|
#5
|
|||
|
|||
|
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. . . |
|
#6
|
|||
|
|||
|
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. . |
|
#7
|
|||
|
|||
|
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. |
|
#8
|
|||
|
|||
|
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. . |
|
#9
|
|||
|
|||
|
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. |
|
#10
|
|||
|
|||
|
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. |
|
#11
|
|||
|
|||
|
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. |
|
#12
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting... | Excel Worksheet Functions | |||
| Counting data - 1 last question | New Users to Excel | |||
| An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions | |||
| Counting question | Excel Discussion (Misc queries) | |||
| Counting values | Excel Worksheet Functions | |||
Linear Mode
