COUNTIF during date range

[sdking]

Hi

Apologies if this has been asked before. I have an audit of test results.

Column A is names.
Row 1 is dates from 1st Jan to 31st Dec.

Pupils have test results on different dates, marks out of 100.

What I would like to do is count the number of results per student row within the last 14 days. Is there a COUNTIF(s), MATCH, HLOOKUP, MATCH -type formula I can use?

Thank you!!

S

 

[Eric W]

Welcome to the MrExcel forum!

Maybe:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		10/1/2021	10/2/2021	10/3/2021	10/4/2021	10/5/2021	10/6/2021	10/7/2021	10/8/2021	10/9/2021	10/10/2021	10/11/2021	10/12/2021	10/13/2021	10/14/2021	10/15/2021	10/16/2021
2	A		1			1		1		1	1	1			1	1	1
3	B		2		2		2			2	2		2			2
4	C		1	1		1		1		1	1		1
5	D				1				1	1	1		1				1
6	E		1	1	1		1		1	1		1				1
7	F	1		1			1	1	1	1		1	1	1		1	1
8	G			1	1	1		1		1	1		1			1
9
10		Scores in last 14 days
11	A	8
12	B	7
13	C	7
14	D	5
15	E	8
16	F	10
17	G	8
Sheet26

Cell Formulas
Range	Formula
B11:B17	B11	=COUNTIFS(INDEX($B$2:$Z$8,MATCH(A11,$A$2:$A$8,0),0),">0",$B$1:$Z$1,">="&TODAY()-14,$B$1:$Z$1,"<="&TODAY())

 

[sdking]

Thanks so much for that!

It worked when I played around with it (adding in extra columns/rows) but as soon as I copied in my actual data from a separate spreadsheet it gives 0 for every row when it's definitely not a 0. Do you know what sort of thing could cause this? I've tried changing the cells between Number, Text, General etc. but nothing really seems to be doing anything.

 

[Sufiyan97]

Check it and verify your data that your numbers are actual numbers

Convert numbers stored as text to numbers - Microsoft Support

When numbers are actually stored in cells as text, problems with calculations or sorting can occur. Read this article to learn how to fix this problem.

support.microsoft.com

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: COUNTIFS returning 0 for every row
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.