counting and percentages in multiple sheets

Hello kmfdm515,

To test for a string value the UDF would be...

Public Function GetTextCnt(RefCell_Addx As String,  Text_Value As String) As Long

  Dim N As Long
  Dim Wks As Worksheet
  
    For Each Wks In Worksheets
      If Wks.Range(RefCell_Addx).Value = Text_Value Then N = N + 1
    Next Wks   

    GetTextCnt = N
    
End Function

Sincerely,
Leith Ross

thanks so much!

what language is this code? VB?

Hello kmfdm515,

The language is VBA (Visual Basic for Applications). This is used by all the Office suite programs: Excel, Word, PowerPoint, Access, and Outlook.

Sincerely,
Leith Ross

expanding on this a bit, would it be possible to then list the sheet names in some cell and specify whether they met the criteria or not?

Hello kmfdm515,

You should start a new thread to get an answer to that question.

Sincerely,
Leith Ross

Thanks Leith, I'll do that.

With these formulas you gave me, though...they aren't re-calculating after any changes are made to the numbers in the sheets, even after saving and re-opening my file. i have to manually go back to my cells with these formulas and press enter for every one to get them to re-calculate.

am i missing something here?

Hello kmfdm515,

I forgot to add an important line of code to the macro. The line in blue makes sure the function gets recalculated whenever Excel recalculates the sheet.

Public Function GetTextCnt(RefCell_Addx As String,  Text_Value As String) As Long

  Application.Volatile

  Dim N As Long
  Dim Wks As Worksheet
  
    For Each Wks In Worksheets
      If Wks.Range(RefCell_Addx).Value = Text_Value Then N = N + 1
    Next Wks   

    GetTextCnt = N
    
End Function

Sincerely,
Leith Ross