Only calculate when finished entering week

[shezi]

I needed help generating a formula week wise. My goal is to have the First Contact (FC) and Follow-up (FU) calculated from Wednesday of a week to the Tuesday of next week by looking at the dates. So, in this case on cell M10 the FC should be 5 and on cell N10 the FU should be 3 and then again it would calculate for the next week from Wednesday until Tuesday so on M16 it should calculate FC as 1 and on N16 the FU as 4. I need to continuously record data for further weeks therefore need a formula which could do so. Would a SUMIF be a good option here?

Currently I have the following formulas:
=COUNTIF(A:A,"First Contact") which calculates 6
=COUNTIF(A:A,"Follow-up") which calculates 7

 

[vletm]

shezi
You could solve that eg this way ... without formulas, no matter of order of data.
Please, next time attach a sample Excel-file too.

 

[bosco_yip]

Try,

1] In C2, formula copied down :

=IF(($A1="")*($B1=""),"",IF($A2<>"","",COUNTIF($A$2:$A2,"First Contract")-SUM(C$1:C1)))

2] In D2, formula copied down :

=IF(($A1="")*($B1=""),"",IF($A2<>"","",COUNTIF($A$2:$A2,"Follow-up")-SUM(D$1:D1)))

 

[shezi]

Try,

1] In C2, formula copied down :

=IF(($A1="")*($B1=""),"",IF($A2<>"","",COUNTIF($A$2:$A2,"First Contract")-SUM(C$1:C1)))

2] In D2, formula copied down :

=IF(($A1="")*($B1=""),"",IF($A2<>"","",COUNTIF($A$2:$A2,"Follow-up")-SUM(D$1:D1)))

View attachment 80371

Thank you so much! That is exactly what I was looking for. Much appreciated