MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


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
Dim WSht As Worksheet

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:


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

OzGrid Business Applications