Counting values in mulitple worksheets

[Joan]

I am trying to count values in a specific cell on multiple worksheets. (The
cell is in the same location on all the worksheets). Something like this:
Count all values = to "Y" in cell A24 on all worksheets.

Anyone have any suggestions?

 

[T. Valko]

It can be somewhat complicated if you have a lot of sheets.

List your sheet names in a range of cells. Assume this list is in the range
G1:G10.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&G1:G10&"'!A24"),"Y"))

It might be easier to just use another cell (the same cell) on each sheet
with this formula:

=--(A24="Y")

Then just use a sum formula like this:

=SUM(Sheet1:Sheet10!A1)

 

[Tim879]

This code works (sort of). It creates a user defined function. the
only bug is that it won't work if the "ref" variable is the same as
the cell the formula is currently in.

To use the formula, enter countif_multiple_tabs("Y", "A24").

Per my first statement, this will work so long as you don't put the
formula in cell A24.


Function countif_multiple_tabs(search_string As String, ref As String)

search_count = 0
For i = 1 To Application.Worksheets.Count
If Worksheets(i).Range(ref).Value = search_string Then
search_count = search_count + 1
Next

countif_multiple_tabs = search_count

End Function