General discussion

Locked

Excel COUNTIF function for 3D workbook.

By Kilo Watt ·
Is there a way to use the COUNTIF function in Excel for 3D calculation of a text entry? Specifically trying to count how many "yes" or "no" answers provided in cell A12 on Sheet1 through Sheet254 with the total appearing on Sheet 255. If COUNTIF can't be used, what formula syntax would give me the solution?

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel COUNTIF function for 3D workbook.

by Peyison In reply to Excel COUNTIF function fo ...

I don't think there's a way to make CountIf work across multiple sheets. Here's two options that might work:

1) On sheet 255 create a list of links to each of the other sheet's cell A12 - so all of the values would be on one sheet. You could thenuse CountIf on this range.

2) Write your own function to do this. Add the following code to a code module in your workbook:

Function Count3D(aiFirstSheet As Integer, aiLastSheet As Integer, _
asCell As String, asValue As String) As Integer

'aiFirstSheet and aiLastSheet are the sheet numbers of the first and last sheets to check
'asCell is the cell address of the cell to check
'asValue is the value in the cell that you want to check for
' only cells that contain this value will be included in the count

Dim I As Integer
Dim iCount As Integer

'this causes Excel to recalculate the function whenever the sheet recalcs
Application.Volatile True

'loop through the sheets and check for the desired value in the specified cell
'you can use UCase() to make the check not case sensitive
For I = aiFirstSheet To aiLastSheet
If ActiveWorkbook.Worksheets(I).Range(asCell).Value = asValue Then
iCount = iCount + 1
End If
NextCount3D = iCount

End Function

In the cell that you want the count to be displayed in, enter:

=Count3D(1, 254, "A12", "yes")

Hope this helps.

Collapse -

Excel COUNTIF function for 3D workbook.

by Kilo Watt In reply to Excel COUNTIF function fo ...

The linking is too labor intensive and counters the efficiency of using a formula.

The code module answer is too far over my head to understand. My users need something closer to plain English, they are not programmers.

Collapse -

Excel COUNTIF function for 3D workbook.

by C_M_S In reply to Excel COUNTIF function fo ...

Hi there,
Before starting make a backup copy of your workbook.
1. Locate a cell that is blank on all sheets (example C13).
2. Select the Sheet1 tab, press and hold "Shift", and select the tab of the last sheet (254).
3. Select the blank cell identified in step 1 (C13).
4. Type "=if(A12 = "Yes", 1)" w/o quotes.
5. On sheet 255 select the cell that you want the total to appear in. Type
"=Sum(sheet1:sheet254!A12)" w/o quotes.

Good Luck,
Mike

Collapse -

Excel COUNTIF function for 3D workbook.

by C_M_S In reply to Excel COUNTIF function fo ...

Sorry,
The formula in step 5 should read
=Sum(sheet1:sheet254!C13)

Collapse -

Excel COUNTIF function for 3D workbook.

by Kilo Watt In reply to Excel COUNTIF function fo ...

Poster rated this answer

Collapse -

Excel COUNTIF function for 3D workbook.

by Kilo Watt In reply to Excel COUNTIF function fo ...

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums