Pieces made between 19 are represented by a letter followed by two numbers. The second number represents the year, and the fourth number represents the factory. The first number represents the month the piece was made. Pieces made between 19 will have three numbers. Therefore, if the series of numbers says “76L,” then the piece is from 1906. The second number is the year in which the pottery was made, and the letter refers to the factory location. If there are two numbers and one letter, the china dates between 19.

ThisWorkbook.Sheets().Determine how many numbers and letters are printed. GetVarSectorCount = (ThisWorkbook.Sheets().Columns(colSector), _ GetVarSectorCount = strSector & ' Not Found' GetVarSectorCount = 'SECTOR Heading Not Found' GetVarSectorCount = 'VAR Heading Not Found'

GetVarSectorCount = 'Incomplete Data in Formula' GetVarCount = (ThisWorkbook.Sheets().Columns(colVar)) - 1įunction getVarSectorCount(rngVar As Range, strSector As String, rngRw As Range) As Variant Set aCell = SectorRange.Find(What:=strSector, LookIn:=xlValues, _ Set SectorRange = ThisWorkbook.Sheets().Columns(colSector) Set aCell = rngRw.Find(What:='Sector', LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ Set aCell = rngRw.Find(What:=rngVar.Value, LookIn:=xlValues, _ Len(Trim(strSector)) = 0 Then Exit Function GetVarCount = 'Incomplete Data in Formula' Paste these two codes in Module Function getVarCount(rngVar As Range, strSector As String, rngRw As Range) As Variant Since your table is not continuous, I would recommend VBA (UDF) approach to this so that you can actually copy the formula down ) You can use a Pivotįor the Var2, Var3, adjust the columns in the formula accordingly.įor example B14 for Var2 will become =COUNTA(D2:D9) and C14 will become =COUNTIFS($B$2:$B$9,$C$12,D2:D9,'') My question is: what formula's can calculate the number of observations in variables columns B and C? Any help is greatly appreciated. Also, the order of variables might not be the same. It is possible that in the variables sheet, there are comments in between different variables (although never in in column A this column holds either a variable name or is empty). Looking solely at banks ( SECTOR = 'Bank') there are 2 observed values ( 5 for ING and 0 for BofA).

Looking at the companies sheet, there are 4 observations for this variable ( 7, 5, 0 and 5). In the sheet below, (1) is in column B, (2) in column C: I need (1) an overall count of the number of observations and (2) the number of observations for companies that are in the banking sector. I need to know how many observations I have for each VAR. There are many missing values (empty cell is missing value, 0 actually is an observed value). This sheet holds hundreds of VARs for thousands of companies.

How do i know if i have qm for excel