Hi Katy,

Some minor points:
1. You should adjust Don's formula by changing <=8 to <8 in the formula
=SUMPRODUCT((A1:A170)*(A1:A17<=8))
2. If you are using 2007 you can write
=COUNTIFS(A1:A17,"0",A1:A17,"<8")
3. If you are dying to use COUNTIF in 2003 or earlier here is another
approach:
=SUM(COUNTIF(A1:A17,{"0","=8"})*{1,-1})
4. You can write a shorter array version of formula 1 as follows:
=SUM((A1:A170)*(A1:A17<8))
This last one requires array entry (press Ctrl+Shift+Enter instead of Enter)

Most likely the COUNTIFS function executes fastest but I haven't checked it,
of course it only works in 2007. Formula 4 generally executes 10-15% slower
than formula 3 but that is only an issue if you have lots of formulas that
slow down your spreadsheet.

Cheers,
Shane Devenshire


"KatyCoxy" wrote:

I tried to post this message yesterday but I'm not sure if it posted,
apologies if it comes up twice.

My question relates to the countif function. I want to count the numbers
that are greater than 0 and less than 8 in one column.

The formula that I have been trying to use, and variations therin is
=COUNTIF(O34:O45,"=0")-COUNTIF(O34:O45,"<8")

This doesn't give me the right answer. I have also tried a variation of the
answer posted to Sassy on 6/14/2007.

Any help would be greatly appreciated.