counting and percentages in multiple sheets

**kmfdm515** · 01-22-2008, 07:43 PM

i apologize in advance for starting 3 threads in 1 day, but i thought each one was different enough from the last top warrant it's own thread.

here's an example of what i am trying to do. i have 4 worksheets with various names. i have an 5th worksheet with a named list (TabNames) of the 4 worksheet names

each of the 4 worksheets have cells with percentages in them.

i would like to check a cell from each sheet and see if the percentage is equal to or above a certain %, for example anything equal to or above 90%. then return the number of cells that meets or exceeds that 90% criteria.

so for example

sheet1 sheet2 sheet3 sheet4
A
1 80% 91% 76% 82%
2 80% 95% 92% 43%

so checking cell A1 in list TabNames would return 1, and checking cellA2 would return 2.

thanks...last topic today i promise

just starting to learn about excel formulas...

**Leith Ross** · 01-22-2008, 09:54 PM

Hello kmfdm515,

The easiest approach is to use a UDF (User Defined Function) to do this across multiple sheets. Here is the code and instructions on how to install and use the UDF.

UDF Macro Code

Public Function GetPercentCnt(RefCell_Addx As String, Compare_Flag As String, Percent_Value As Double) As Long

  Dim N As Long
  Dim Wks As Worksheet
  
    For Each Wks In Worksheets
      Select Case Compare_Flag
        Case Is = "<="
          If Wks.Range(RefCell_Addx) <= Percent_Value Then N = N + 1
        Case Is = "<"
          If Wks.Range(RefCell_Addx) < Percent_Value Then N = N + 1
        Case Is = "="
          If Wks.Range(RefCell_Addx) = Percent_Value Then N = N + 1
        Case Is = ">"
           If Wks.Range(RefCell_Addx) > Percent_Value Then N = N + 1
       Case Is = ">="
          If Wks.Range(RefCell_Addx) >= Percent_Value Then N = N + 1
     End Select
    Next Wks
    
    GetPercentCnt = N
    
End Function

Installing the UDF Code
1. Copy the code above using CTRL+C
2. Open your Workbook in Excel.
3. Right Click on the Name Tab of any Worksheet.
4. Click View Code in the pop up menu.
5. Press ALT+I to activate the Insert menu.
6. Press the letter m.
7. Paste the code into the module using CTRL+V
8. Save the UDF code using CTRL+S
9. Press ALT+Q to return to Excel.

Using the UDF
This function takes 3 arguments:
RefCell_Addx - A string indicating the cell you want to check
Compare_Flag - A string indicating the type of compare to perform. There are five strings "<=", "<", "=", ">", ">="
Percent_Value - Number expressed as a valid percentage.

Example

'Sheet5 cell B1 has the formula 
  =GetPercentCnt("A1", ">=", 90%)

'Sheet5 cell B2 has the the formula
  =GetPercentCnt("A2", ">=", 90%)

Sincerely,
Leith Ross

**kmfdm515** · 01-22-2008, 11:26 PM

That is fantastic! Thank you so much!

One more question...how do I edit that formula to make it conditional on some text in a certain cell on each sheets? like if i only want it to count sheets that have text "x" in cell C9, etc.

**Leith Ross** · 01-23-2008, 12:00 AM

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

**kmfdm515** · 01-23-2008, 12:15 AM

thanks so much!

what language is this code? VB?

**Leith Ross** · 01-23-2008, 01:52 AM

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

counting and percentages in multiple sheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

counting and percentages in multiple sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions