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]

Thanks a lot! It's really great, and advanced. I was trying to understand how the B8 formula works Can you please explain how the blue parts work? What do the zeros after the second commas refer to? And How does C8 there work? It's value is 3, but what is done with this value? I was thinking it is the row number, but row 3 in Scores is for student2.

 

[Peter_SSs]

Thanks a lot! It's really great, and advanced. I was trying to understand how the B8 formula works Can you please explain how the blue parts work? What do the zeros after the second commas refer to?

The blue part is the arguments in the INDEX function. The syntax of INDEX is
INDEX(Range, Row, Column)
INDEX(Scores!A$2:AF$10,C8,0)
The row is drawn from C8, see further comments below.
When zero is used for the column, it means to use all columns in the range. That is, a whole row in the range

And How does C8 there work? It's value is 3, but what is done with this value? I was thinking it is the row number, but row 3 in Scores is for student2.

You are on the right track about C8 being the row number. However, it is not the row number on the worksheet, but rather the row number in the range being referred to. In my formula the range is A2:AF10 in the Scores sheet. When C8 = 3 it means the 3rd row in that range. That is, A4:AF10, which is the row for student3.

 

[Rnkhch]

I see, wow, that's a really awesome technique! You are a pro Now I see how the index is used to simplify the formula because it is a number rather than text in the case of student names, am I correct?

But out of curiosity and extra Excel learning, how could we do this if we had not defined the index and just used student names (assuming there are no duplicate names)?

 

[Peter_SSs]

You would basically have to put the formula from C8 into the B8 formula in the two places that C8 appears in that formula.
The formula in B8 would become the longer & more unwieldy & more resource-hungry ...
=IFERROR(COUNTIFS(Scores!A$1:AF$1,">="&B$3,Scores!A$1:AF$1,"<="&B$1,INDEX(Scores!A$2:AF$10,MATCH(A8,Scores!A$2:A$10,0),0),">"&B$4,INDEX(Scores!A$2:AF$10,MATCH(A8,Scores!A$2:A$10,0),0),"<"&B$5),"")

 

[Rnkhch]

I see. Thanks a lot for all the help and information. You're number one

I'm gonna work on my score sheets now and will post back if anything comes up.

 

[Peter_SSs]

Thanks a lot for all the help and information.

No problem.

 

[Rnkhch]

Hi Peter,

Would it be possible to have an "Average" column in Analysis to display the average of the scores that the previous formula counted (if the count is not zero)?

Thanks a lot

 

[Peter_SSs]

Hi Peter,

Would it be possible to have an "Average" column in Analysis to display the average of the scores that the previous formula counted (if the count is not zero)?

Thanks a lot

Assuming the Count is in B8 & the Idx in C8, try
=IF(N(B8),AVERAGEIFS(INDEX(Scores!$A$2:$AF$10,C8,0),Scores!A$1:AF$1,">="&B$3,Scores!A$1:AF$1,"<="&B$1,INDEX(Scores!A$2:AF$10,C8,0),">"&B$4,INDEX(Scores!A$2:AF$10,C8,0),"<"&B$5),"")

 

[Rnkhch]

Thanks a lot! I think with one more addition, my analysis will be complete

Can I also have a second average column, where I get the average of all scores within the date ranges? That way I can see how the first average (which is the average within the specified score range and date range) compares with the average of all scores in the date ranges. This will help me gauge the students' performance.

Thank you so much!

 

[Peter_SSs]

Can I also have a second average column, where I get the average of all scores within the date ranges?

Just take out the parts of the last formula that restricts the size of the scores.

=IF(N(B8),AVERAGEIFS(INDEX(Scores!$A$2:$AF$10,C8,0),Scores!A$1:AF$1,">="&B$3,Scores!A$1:AF$1,"<="&B$1<del>,INDEX(Scores!A$2:AF$10,C8,0),">"&B$4,INDEX(Scores!A$2:AF$10,C8,0),"<"&B$5</del>),"")