Only calculate when finished entering week-CodePudding

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

CodePudding user response：

Since your data is sorted you could use the following:

In M10 use: =COUNTIF($A$1:$A10,"First Contact")-SUM(M$1:M9)

And in N10 use: =COUNTIF($A$1:$A10,"Follow-up")-SUM(N$1:N9)

And since your results show in an empty row, if you want a draggable solution, wrap it in IF like this: =IF($A10="",COUNTIF($A$1:$A10,"FC")-SUM(M$1:M9),"")

The $ in the range reference for column letter and row number will lock the position if you drag the formula or copy it and paste it elsewhere.

CodePudding user response：

Formula in cell C2:

=IF(A2<>"";"";COUNTIFS(A:A;"First Contact";B:B;"<="&B1;B:B;">="&B1-6))

Formula in D2:

=IF(A2<>"";"";COUNTIFS(A:A;"Follow_up";B:B;"<="&B1;B:B;">="&B1-6))

Drag to bottom.