ValerieT
04-25-2014, 08:11 AM
C_Ids
C_Dat
C_Obj
C_Key
C_Tot
Expected
1
01/01/2013
A
1
01/03/2013
A
1
01/01/2014
D
D-A
Not counting
1
2
01/01/2012
A
3
01/01/2012
D
3
01/03/2013
E
E-D
Not counting
2
3
01/01/2014
D
D-E
Not counting
2
4
01/01/2014
A
4
01/12/2014
D
D-A
Not counting
1
I'm having a problem to understand how countifs works, and Internet gives me different syntax. [from memory: either (range, criteria 1, criteria n) or (range1, criteria1, range n ,criteria n) ]
I'm trying to count in C_Tot for each employee (C_Ids) the number of non empty columns in C_Keys
For i = 3 To Endlign
Cells(i, C_Tot) = ""
My_Count = 0
If Cells(i, C_Key) <> "" Then
My_Count = Application.CountIfs(Range(Cells(2, C_Key), Cells(Endlign, Ids)), Cells(i, C_Ids) = Cells(i - 1, C_Ids))
If My_Count > 0 Then
Cells(i, C_Tot) = My_Count
Else
Cells(i, C_Tot) = "Not counting"
End If
End If
Next i
Thanks in advance for your precious and so much appreciated help
C_Dat
C_Obj
C_Key
C_Tot
Expected
1
01/01/2013
A
1
01/03/2013
A
1
01/01/2014
D
D-A
Not counting
1
2
01/01/2012
A
3
01/01/2012
D
3
01/03/2013
E
E-D
Not counting
2
3
01/01/2014
D
D-E
Not counting
2
4
01/01/2014
A
4
01/12/2014
D
D-A
Not counting
1
I'm having a problem to understand how countifs works, and Internet gives me different syntax. [from memory: either (range, criteria 1, criteria n) or (range1, criteria1, range n ,criteria n) ]
I'm trying to count in C_Tot for each employee (C_Ids) the number of non empty columns in C_Keys
For i = 3 To Endlign
Cells(i, C_Tot) = ""
My_Count = 0
If Cells(i, C_Key) <> "" Then
My_Count = Application.CountIfs(Range(Cells(2, C_Key), Cells(Endlign, Ids)), Cells(i, C_Ids) = Cells(i - 1, C_Ids))
If My_Count > 0 Then
Cells(i, C_Tot) = My_Count
Else
Cells(i, C_Tot) = "Not counting"
End If
End If
Next i
Thanks in advance for your precious and so much appreciated help