Counting how many cells are greater than 0 on several worksheets

**tiggerny** · 02-27-2010, 04:30 PM

Ok I have a huge workbook created. Each worksheet has a different client. Under each client there is a grid of the difference services and how many hours there are each day (for the quarter). That all sums up to the total hours at the end of the week. Now what I want to do is to perform a function that will count how many clients received a particular service in that quarter. I tried

=COUNTA(A:Z O21,">0.0")
=COUNT(A:Z O21,">0.0")

These are close but not totally up.
=SUMIF(A:Z O21,">0.0") That one was way wrong

I thought about doing a hidden formula but I send the form out to other agencies and I do not want to chance that because they do not see it, it gets changed. I guess worse comes to worse I could lock it. But it just seems to me that this can be done!

**jeffreybrown** · 02-27-2010, 04:44 PM

Hi & Welcome to the Board,

Here is how you would count anything greater than 0...

=COUNTIF(E1:E5,">0")

**teylyn** · 02-28-2010, 12:45 AM

Hi, you're missing the ! sign in your cell reference

=COUNTA(Sheet1:Sheet3!A1)

=COUNTA(A:Z!O21)

Counta only counts non empty cells. No other condition possible.

If you want to count with a condition, you should use Countif. Unfortunately, Countif does not work on 3D ranges. You'll need to install the morefunc.xll, which has a countif.3d formula. You can find morefunc.xll here http://download.cnet.com/Morefunc/30...-10423159.html

**tiggerny** · 03-01-2010, 09:59 PM

Thank you so much for your help! It all makes sense why this was not working, but I am still scratching my head to find out if there a way to accomplish what I need (downloading morefunc.xll is not an option as this will go out to several people that I am assuming would have to download it also).

So referring back to my original problem, I tried a few other things that do not seem to work. On each tab, I created a new column and perform an if function =IF(O21>0.1,"1 ","0") for each service that could be provided for the client. Then on the summery page I created a sum formula for those corresponding answers (numbers) =SUM(a:z!Q22) but that does not seem to work either. Is this because you can't sum and if formula even if you make it a number.

Does anyone know how I can get this information that I need without going through each tab quarterly?

**teylyn** · 03-01-2010, 10:09 PM

if this is the formula you're using

=IF(O21>0.1,"1 ","0")

you are creating text, not numbers. SUM() does not sum text. Try

=IF(O21>0.1,1,0)

then the SUM(a:z!Q22) should work.