countifing a range of values i.e. 6-8

[waxb18]

i am trying to simplify a countif that im doing.

Ok...
i need to count if a value is a 6 7 or 8 in a column.
What im doing at the moment is a countifs to bring up 6's, another for the 7s and ANOTHER for the 8s
then suming these together to give me the figure i need..

Buy surely there must be a single countif statment i can use to work it all out at once

 

[VoG]

Try like this

=COUNTIF(A1:A100,">=6")-COUNTIF(A1:A100,">8")

 

[Haldoug]

Good instructions except to include 6s, the instruction need to be
=COUNTIF(A1:A100,">5")-COUNTIF(A1:A100,">8")

 

[VoG]

Good instructions except to include 6s, the instruction need to be
=COUNTIF(A1:A100,">5")-COUNTIF(A1:A100,">8")

I edited my post

 

[barry houdini]

For a small range like 6, 7, 8 you could do that like this

=SUM(COUNTIF(A1:A100,{6,7,8}))

 

[waxb18]

All brilliant suggestions..

But im gonna go withh VoG's solution

POW!!!

 

[Haldoug]

Just another question, what exactly does the { } do?

Thanks

 

[barry houdini]

{6,7,8} is an "array constant" - an array with fixed values rather than calculated values (or values from a cell reference).

In this case using the array constant as the 2nd argument in COUNTIF results in an array of the same size as the result, each element being the count of 6s, 7s and 8s in the range, e.g. if there were 2x6s, 5x7s and 14*8s in the range then

=COUNTIF(A1:A100,{6,7,8})

would give the result as {2,5,14}

...and then the SUM function is used to sum that array - giving a result of 21

 

[Yahya]

to make it more dynamic
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style=";">Count From </td><td style=";">To</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">COUNTIF(<font color="Red">A1:A20,ROW(<font color="Green">INDIRECT(<font color="Purple">C2&":"&D2</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />