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!

 

[Muhammad_Usman]

I really liked Muhammad's solution. I think it's the easiest and most flexible as it lets me indicate both the beginning and end dates very easily. Just one thing is that it would be awesome if I could set the beginning date as a certain date or TODAY(), and indicate the end date by going back in time by the specified number of weeks (opposite of the current algorithm where it comes forward in time form the start date of 04/13/2018). I tried to change the formulas like below, but now it doesn't work, and I get all zeros

[TABLE="width: 161"]
<tbody>[TR]
[TD]Beginning Date[/TD]
[TD="align: right"]TODAY()[/TD]
[/TR]
[TR]
[TD]Week[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Closing Date[/TD]
[TD="align: right"]B1-(B2*7)[/TD]
[/TR]
</tbody>[/TABLE]

Glad To Help You, Here is you desired Formula! Don't forget to post your experience after try this one.!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Begning Date	8/15/2018
2	Week	4
3	Closing Date	7/18/2018
4
5		Total	8/10/2018	8/3/2018	7/27/2018	7/20/2018	7/13/2018	7/6/2018	6/29/2018	6/22/2018	6/15/2018	6/8/2018	6/1/2018	5/25/2018	5/18/2018	5/11/2018	5/4/2018	4/27/2018	4/20/2018
6	student1	4	90	90	90	90	90	90	90	90	90	100	100	100	100	100	100	100	100
7	student2	0	70	70	70	70	70	70	70	70	70	85	85	85	85	85	85	85	85
8	student3	1	8	73	65	82	111	132	153	175	196	218
Sheet6

Cell Formulas
Range	Formula
B1	=TODAY()
B3	=B1-(B2*7)
B6	=COUNTIFS($C$5:$AG$5,"<="&$B$1,$C$5:$AG$5,">="&$B$3,C6:AG6,">80",C6:AG6,"<100")
B7	=COUNTIFS($C$5:$AG$5,"<="&$B$1,$C$5:$AG$5,">="&$B$3,C7:AG7,">80",C7:AG7,"<100")
B8	=COUNTIFS($C$5:$AG$5,"<="&$B$1,$C$5:$AG$5,">="&$B$3,C8:AG8,">80",C8:AG8,"<100")

 

[Peter_SSs]

Here is you desired Formula!

I'm not so sure.

(It's a growing table such that each week I add a new column at where column B is.)

With the layout you posted the OP would be adding a new column where column C is.

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")

Doesn't your formula have exactly the same problem? As soon as a new column C is inserted (with heading 17 August 2018) your formula in cell B6 will be ..
=COUNTIFS($D$5:$AH$5,"<="&$B$1,$D$5:$AH$5,">="&$B$3,D6:AH6,">80",D6:AH6,"<100")
.. thereby not checking the values in column C without manually adjusting the formulas each week.

 

[Rnkhch]

Thank you guys! I tested both, and Peter's solution doesn't need updating after inserting new columns, so that's wonderful ​

.

I made an extra adjustment in B1 by adding this formula:

=TODAY()-WEEKDAY(TODAY(),2)+5

Now it shows the Friday of the current week as the beginning of the range. I hope the formula is correct for this purpose. I'll have to check everyday till Sunday to make sure If you guys can double check that, that would be awesome too ​

 

[Peter_SSs]

You are welcome.

Your adjustment looks like it should be okay. You'll just need to check as the days pass.

I did think you wanted the option of counting backwards or forwards, but I must have been mistaken about that?

There is one other thing you should check as my formulas and Muhammad_Usman's differ in another way. Suppose the "current date" in column C is 17 August and cell B2 indicates 2 weeks. My formula will check 2 columns only (17 August & 10 August) whereas Muhammad_Usman's formula will go back 2 weeks from 17 August to 3 August, which means 3 columns would actually be counted. If my interpretation is wrong my cell B3 formula would need amending.

 

[Rnkhch]

Yes, 2 weeks is correct in the example you mentioned, so it's working pretty well

I would only need to count backwards, at least for now. So perhaps you can send a version of the formula that takes positive numbers as backwards (and negatives as forward)? That way I can just put a number like 2 or 5 or 10 and only use negative numbers for forward if the need comes up at some point.

Thanks a lot!

 

[Peter_SSs]

So perhaps you can send a version of the formula that takes positive numbers as backwards (and negatives as forward)?

This just counts backwards. In B2

=B1-(B2-1)*7

 

[Rnkhch]

Awesome, thanks!

 

[Peter_SSs]

Awesome, thanks!

You're welcome.

 

[Rnkhch]

One last thing Would it be possible to have this analysis in a separate sheet named "Analysis" which would contain the names of students, while the table of names and scores would be in a different sheet called "Scores"? I would need your formula to take the name of the student from the Analysis sheet, match it to the same student in the Scores sheet, and then do the calculation. It will be awesome if you can help me with this capability if it's not too much work; if it's too complicated, don't worry about it. I can get by with the current format.

Thanks a lot ​

 

[Peter_SSs]

Would it be possible to have this analysis in a separate sheet named "Analysis" which would contain the names of students, while the table of names and scores would be in a different sheet called "Scores"?

Sure. Here is one way.

My layout of 'Scores'. If yours is different, you will need to adjust my formulas below or post back with details.
In future weeks just insert a new column B as you originally described.

Excel Workbook
	A	B	C	D	E	F	G
1	Name	10/08/2018	3/08/2018	27/07/2018	20/07/2018	13/07/2018	6/07/2018
2	student1	90	90	90	90	90	90
3	student2	70	99	70	70	70	70
4	student3	8	73	65	82	111	132
5
Scores

In 'Analysis' I would use a helper column (C) to keep the other formulas a bit simpler. The helper column can be hidden once the formulas have been entered.

Formulas in B8:C8 copied down as far as you might need after adjusting the $10 references in the formulas to be at least as big as the last row of data in 'Scores'.

Excel Workbook
	A	B	C
1	Begning Date	10/08/2018
2	Weeks (+/-)	3
3	Closing Date	27/07/2018
4	Min	80
5	Max	100
6
7		Count	Idx
8	student3	0	3
9	student1	3	1
10	student4
11	student2	1	2
12
Analysis