Counting values of the same cell in numerous sheets excluding values that are 0

Posted by Mireille on April 22, 2001 9:26 AM

In a workbook where the number of sheets are variable I want to count all the A1 cells. If the value of the cell = "0" (not blank) then I do not want to count it.
How can i do this?

Start = First sheet I want to count
End = Last sheet I want to count
=COUNTA(Start:End!A1,">0")

Thanks,
Mireille

Posted by Dave Hawley on April 22, 2001 10:59 AM

Hi Mireille

As the number of sheets will be variable I would use a Custom function for this.

Function CountCells(CountCell As Range, CriteriaText As String)
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim WSht As Worksheet

Application.Volatile
For Each WSht In ActiveWorkbook.Worksheets
CountCells = WorksheetFunction.CountIf _
(WSht.Range(CountCell.Address), CriteriaText) + CountCells
Next WSht

End Function

To use this Push Alt+F11 and go to Insert>module and paste in the code.
Push Alt+Q and then in the cell you want your result put:

=CountCells(A1,">0")

This will count cell A1 on ALL Worksheets that are >0.

Dave
OzGrid Business Applications