COUNTA for a dynamic range

[cherry_pie]

For each row in a worsheet, I want to count the number of cells with text in them. I know the basic formula for doing the count using stable cell references [ie =COUNTA(D2:E2], but I want to make the range start from the column with today's date in it, and finish in 14 days time, so I get the count for the next 14 days.

I have used Match in a formula to return the column number that has today's date in it, but I don't really know how to progress here and I'm clearly not googling the right phrases to find the answer.

Any help would be appreciated. If it isn't possible, or requires VBA then I can find a workaround, but I'm keen to see if it is possible and improve my knowlege.
Also happy to be directed to previous posts / guidance articles if that's easier.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also does row 1 have the dates?

 

[cherry_pie]

Hi,
Thanks for such a speedy reply.
Updated my profile as requested.
The dates are in row 7, and start quite a few columns over, say F7.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
7				17/10/2021	18/10/2021	19/10/2021	20/10/2021	21/10/2021	22/10/2021	23/10/2021	24/10/2021	25/10/2021	26/10/2021	27/10/2021	28/10/2021	29/10/2021	30/10/2021	31/10/2021	01/11/2021	02/11/2021	03/11/2021
8	12			a	b	c		e		g		i	j	k	l	m	n	o	p	q	r
Main

Cell Formulas
Range	Formula
C8	C8	=COUNT(FILTER(COLUMN(F8:W8),(F7:W7>=TODAY())*(F7:W7<=TODAY()+14)*(F8:W8<>"")))

 

[cherry_pie]

Hi,
That is bringing back zero unfortunately.

 

[Fluff]

Is that how your data is laid out?

 

[cherry_pie]

Yes, other than it has multiple columns and I need a count for each row, how many cells in the next 14 days have a character in them.
It is formatted as a table also.
I have just changed it from a table to a normal range (as I was writing this reply), and it works now.

 

[Fluff]

If the dates are the header row in a table then they are text & not real dates, which is why it didn't work.

 

[cherry_pie]

Ahh, thank you. I wasn't aware it considered them like that, so that's really helpful to know for now and in the future.
Your help is much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.