How to use COUNTIFS in between "current week" and "the specified week"?

[Rnkhch]

Hello,

In my tables of scores, I sometimes need to know how many times each student had scores that were below or above a particular score or within a certain range. (It's a growing table such that each week I add a new column at where column B is.) All weeks are indicated per column by the Friday of that week.

I came up with this formula below that counts the scores meeting the criteria for each student. However, this formula scans the entire row:

=COUNTIFS(5:5,">80",5:5,"<100")

And if I specify a range, then I have to manually update the formulas each week because of addition of a new column each week:

=COUNTIFS(B5:AF5,">80",B5:AF5,"<100")

Given that my columns have the week identifier, is there any way I can specify a date range for my formula such that the beginning week of the range is the "current week" and the end week of the range is a week number I specify in a cell as shown below? In this example, I want the formula to scan 20 weeks starting from the current week:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]begin:[/TD]
[TD]current week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]end:[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8/10/2018[/TD]
[TD]8/3/2018[/TD]
[TD]7/27/2018[/TD]
[TD]7/20/2018[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]student1[/TD]
[TD]87[/TD]
[TD]91[/TD]
[TD]72[/TD]
[TD]79[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]student2[/TD]
[TD]98[/TD]
[TD]97[/TD]
[TD]90[/TD]
[TD]92[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]student3[/TD]
[TD]8[/TD]
[TD]73[/TD]
[TD]65[/TD]
[TD]82[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks a lot for any input!

 

[Rnkhch]

Oh yes, I should have thought of that

Thanks for all the help! I have an amazing tool now to analyze my scores I'll reach out if I think of cool additions

 

[Rnkhch]

Hi Peter,

I don't actually need this for my analysis, but I just thought of it the other day and tried to solve it myself, but have had no luck so far so I thought to reach out

If I ever wanted to get the standard deviation of the scores in your post #28 (which is the average of all scores between specified date range and min and max range), how can it be done? If there was an "STDEVIFS" function, it would have been super easy but since that doesn't exist, I tried to use IF, but kept getting errors.

Thanks a lot!

 

[Peter_SSs]

If the number of students is not too large, you could consider this set of alternative formulas. They use the volatile function OFFSET, which is why I mentioned the number of students. If volatile functions are used too prolifically in your worksheet, it can cause the worksheet's calculation to slow significantly. Anyway, give it a go if you want as the formulas are a bit shorter to read/write.

My 'Scores' sheet is as shown in post 20.

'Analysis' has a new helper cell in C1. All other formulas are copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	Begning Date	10/08/2018	1
2	Weeks (+/-)	3
3	Closing Date	27/07/2018
4	Min	80
5	Max	100
6
7			Idx	Count	"Between" Av	All Av	StdDev
8	student3		3	0		48.666667	28.940552
9	student1		1	3	90	90	0
10	student4
11	student2		2	1	99	79.666667	13.670731
Analysis

 

[Rnkhch]

Awesome! Phenomenal. Thanks a lot

 

[Peter_SSs]

Awesome! Phenomenal. Thanks a lot

Cheers.