Countifs syntax and use, beginner question

[ValerieT]

C_Ids

C_Dat

C_Obj

C_Key

C_Tot

Expected results



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 try to use countifs, but can't make it right, and Internet gives me different syntaxes

is it:
COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2], etc... ]
or
expression.CountIfs(Arg1, Arg2, Argn...) with Arg1 = Range and Arg 2 to n = Criteria


In this example, I want to count not empty cells in C_Key for each employee (C_Ids)

Can't make it work after several attempts

'COUNT MOVEMENT (Object)
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, C_Key)), 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, I know you always help, even stupid basic question

[mancubus]

seeing the entire code would be of more help.

first four variables are added for testing purposes based on my assumptions below.
Endlign = 10
C_Tot = 5
C_Key = 4
C_Ids = 1


adr1, adr2 and crit are string variables. declare them in the code.
crit = """""" = nullstring



Sub x()


Endlign = 10
C_Tot = 5
C_Key = 4
C_Ids = 1

adr1 = Range(Cells(2, C_Ids), Cells(Endlign, C_Ids)).Address(0, 0)
adr2 = Range(Cells(2, C_Key), Cells(Endlign, C_Key)).Address(0, 0)
crit = """"""

For i = 2 To Endlign
If Cells(i, C_Key) = "" Then
Cells(i, C_Tot) = ""
Else
My_Count = Evaluate("=SumProduct(--(" & adr1 & "=" & Cells(i, C_Ids) & "),--(" & adr2 & "<>" & crit & "))")
If My_Count > 0 Then
Cells(i, C_Tot) = My_Count
Else
Cells(i, C_Tot) = "Not counting"
End If
End If
Next i




End Sub

[snb]

In Expected results:
In E2:


=COUNTIFS($A$1:$A$13;$A1;$D$1:$D$13;">""")

[ValerieT]

I'll use mancubus solution, it works.. now I need to understand it :-)

Thanks all!

[Bob Phillips]

You could use this formula that does work

=IF(D2="","",COUNTIFS($A$2:$A$10,$A2,$D$2:$D$10,"<>"))