Excel formula for counting nr records with data and related empty fields
 

Hello,

Somebody can help me out with a formula.

Sheet 1 column B M (Formula with result)
test1 2 / 1
test2 1 / 0
test3 ""

Sheet 2 Column A K
test8 01-03-2012
test2
test1 04-03-2012
test10 05-04-2012
test1
test6

As you can see I need a formula I think (?) based on "Countifs".
In sheet 1 column M I like to have a formula to fill that shows the
number of records in sheet 2 column A with the same text as registered
in the datafield column B of sheet 1.
After this result a 'slash' and there after the results of those same
datafield but then how many of the found identical records have a
enddate registered.

The result of 'test3' is empty because this is not existing in Sheet2.

You get for example "4 / 2". Means: 4 found and 2 of them finished.

regards,
Johan.

Excel formula for counting nr records with data and related empty fields
 

The following user-defined function should work.

Function MyCountIf(Criteria As Range, Search As Range) As String
Dim iCt As Integer
Dim iCt2 As Integer
Dim c As Range

For Each c In Search
If c = Criteria.Value Then
iCt = iCt + 1
If IsEmpty(c.Offset(0, 10)) = False Then iCt2 = iCt2 + 1
End If
Next c
MyCountIf = iCt & "/" & iCt2
End Function

Excel formula for counting nr records with data and related empty fields
 

Thanks for the quick reply.

I mis the reference to the mentioned sheets and columns.

The loopfunction is in sheet1: Column B is the reference and Column M
gives the result.
The formula looks in Sheet2 where the counting of the required text
comes from Column A and the counting for the finish date comes from
Column K.

You understand that I need more help :)

regards,
Johan.

Excel formula for counting nr records with data and related empty fields
 

Maybe you don't understand how to use the function.
Select the cell where you want the result. Using the function prompts
you to select the Criteria and the Search.