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

[johan]

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.

 

[merjet]

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

 

[johan]

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.

 

[merjet]

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.