Progress tracker - provide range to COUNTIF using INDEX and MATCH -

[LukeDF]

I looked through the threads but couldn't find anything. Any help or pointer much appreciated!!

I have to track progress of a bunch of activities based on today's date in Excel without using VB.

The date in B2 is used to count the number of TBD / WIP / DONE in the columns upto the date so the range is C6:G13 when the date is 19Nov but would change to C6:J13 if I changed it to 06Dec

I used a combination of SUBSTITUTE, INDEX, MATCH to get the range C6:G13 but it changes to "C6:G13" when I reference it.

Progress tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1						Total	Overdue	TBD	WIP	Done		Progress to plan
2	As on date	19/11/2021				11	1	0	4	6		55%
3
4	Activity	Date	18Oct	25Oct	01Nov	08Nov	15Nov	22Nov	29Nov	06Dec	13Dec	20Dec	27Dec	03Jan
5		Wk.No	W1	W2	W3	W4	W5	W6	W7	W8	W9	W10	W11	W12
6	Activity 1	Amy		Done
7	Activity 2	Bob		Done
8	Activity 3	Charlie		Done
9	Activity 4	Dan		Done
10	Activity 5	Emmy	TBD		Done	WIP	WIP		TBD
11	Activity 6	Frodo				Done
12	Activity 7	Gigi					WIP		TBD
13	Activity 8	Homer					WIP		TBD
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TODAY()
F2	F2	=COUNTA(C6:G13)
G2	G2	=COUNTIF(C6:F13,"TBD")
H2	H2	=COUNTIF(G6:G13,H1)
I2	I2	=COUNTIF(C6:G13,I1)
J2	J2	=COUNTIF(C6:G13,J1)
L2	L2	=((G2*-200)+(I2*50)+(J2*100))/(F2*100)
D4:N4	D4	=C4+7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:N13	Cell Value	="WIP"	text	NO
C6:N13	Cell Value	="Done"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C6:N13	List	TBD,N/A,WIP,Done

 

[earthworm]

Instead of inputting single date I suggest to add a date range I.E from date and to date
then you can apply logic easily

 

[Fluff]

For F2 how about

=COUNTA(C6:INDEX(C13:N13,MATCH(B2,C4:N4,1)))

 

[earthworm]

For F2 how about

=COUNTA(C6:INDEX(C13:N13,MATCH(B2,C4:N4,1)))

I think he wants the calculation range to shift to between two dates. he want to count TBD , WIP etc between two dates.

 

[Fluff]

That's not my understanding. We'll just have to wait for the OP.

 

[LukeDF]

Earthworm is right. I don't want to manually change the cell ranges everyday. I would like the N in C13:N13 to change whenever the date in B2 changes.

 

[earthworm]

For F2 how about

=COUNTA(C6:INDEX(C13:N13,MATCH(B2,C4:N4,1)))

I think he wants the calculation range to shift to between two dates. he want to count TBD , WIP etc between two dates.

Earthworm is right. I don't want to manually change the cell ranges everyday. I would like the N in C13:N13 to change whenever the date in B2 changes.

You need to input two data for the things to work easily. Is that possible ?

 

[Fluff]

I would like the N in C13:N13 to change whenever the date in B2 changes.

Did you actually try the formula I suggested, or just reject it out of hand? It will only count the values in columns C upto the column with the relevant date.

 

[LukeDF]

Did you actually try the formula I suggested, or just reject it out of hand? It will only count the values in columns C upto the column with the relevant date.My mistake.

My mistake. When I put the formula in the actual spreadsheet, i did not change the INDEX. It works perfectly now. Thanks a lot.

 

[Fluff]

Glad we could help & thanks for the feedback.