Formula COUNTIFS

[Nick van Staden]

Need Help on the countifs formula based on a running count if action date is grater than start date.

I am using formula: =COUNTIF($A$1:A2,A2) to show a running count of unique values in column A however need this formula to only start calculation if

action date is grater than start date.

Example: User ref 2 first action date if prior to start date so must not count this record.
​

[TABLE="width: 604"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD]USER_REF[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 110"]
<tbody>[TR]
[TD]ACTION_DATE[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 94"]
<tbody>[TR]
[TD]STARTDATE[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 140"]
<tbody>[TR]
[TD]USER_REF _COUNT[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 148"]
<tbody>[TR]
[TD]COUNTIF_ACTION DATE > START_DATE[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="align: right"]2019/01/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 94"]
<tbody>[TR]
[TD="align: right"]2019/01/25[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]1[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="align: right"]2019/02/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 94"]
<tbody>[TR]
[TD="align: right"]2019/01/25[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="align: right"]2019/03/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 94"]
<tbody>[TR]
[TD="align: right"]2019/01/25[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="align: right"]2019/01/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 94"]
<tbody>[TR]
[TD="align: right"]2014/01/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="align: right"]2019/02/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 94"]
<tbody>[TR]
[TD="align: right"]2014/01/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="align: right"]2019/01/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]<strike></strike>[TABLE="width: 94"]
<tbody>[TR]
[TD="align: right"]2014/01/01[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
_{^{<strike>
</strike>}}​

<strike></strike>

 

[Eric W]

Try:

=SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2>=$C$2:$C2))

 

[Nick van Staden]

Try:

=SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2>=$C$2:$C2))

Works perfect. Highly appreciated thanks!

 

[Nick van Staden]

Is there a possiblilty to add another criteria to this formula, for example only sum product if column F = true?

I have tried adding the below to the formula however this does not seem to work.

=SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2>=$C$2:$C2),F:F,"True"))​

 

[Eric W]

Try:

=SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2>=$C$2:$C2),--($F$2:$F2="True"))

If you're looking for the value TRUE instead of a text value of "True", remove the quotes from around True in the formula.