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

R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Hi R,

Could you use this? The catch is its easier to work with this type of data when you have Dates & Scores as entries vs Students as columns. If needed you can drag the weeks down to 20.


Book1
ABCDEF
1DATESSTUDENT1STUDENT2STUDENT3
220-07-18799282
327-07-18729065
403-08-18919773
510-08-1887988
6
7
8START DATE20-07-18MIN SCORE80
9MAX SCORE100
10
11
12STUDENT1STUDENT2STUDENT3
13WEEK116-07-1820-07-18011
14WEEK223-07-1827-07-18010
15WEEK330-07-1803-08-18110
16WEEK406-08-1810-08-18110
Sheet1
Cell Formulas
RangeFormula
B13=DATE(YEAR($B$8),MONTH($B$8),DAY($B$8)-WEEKDAY($B$8)+2)
B14=B13+7
B15=B14+7
B16=B15+7
C13=B13+4
C14=C13+7
C15=C14+7
C16=C15+7
D13=COUNTIFS(B$2:B$5,">"&$E$8,B$2:B$5,"<"&$E$9,$A$2:$A$5,">="&$B13,$A$2:$A$5,"<="&$C13)
D14=COUNTIFS(B$2:B$5,">"&$E$8,B$2:B$5,"<"&$E$9,$A$2:$A$5,">="&$B14,$A$2:$A$5,"<="&$C14)
D15=COUNTIFS(B$2:B$5,">"&$E$8,B$2:B$5,"<"&$E$9,$A$2:$A$5,">="&$B15,$A$2:$A$5,"<="&$C15)
D16=COUNTIFS(B$2:B$5,">"&$E$8,B$2:B$5,"<"&$E$9,$A$2:$A$5,">="&$B16,$A$2:$A$5,"<="&$C16)
E13=COUNTIFS(C$2:C$5,">"&$E$8,C$2:C$5,"<"&$E$9,$A$2:$A$5,">="&$B13,$A$2:$A$5,"<="&$C13)
E14=COUNTIFS(C$2:C$5,">"&$E$8,C$2:C$5,"<"&$E$9,$A$2:$A$5,">="&$B14,$A$2:$A$5,"<="&$C14)
E15=COUNTIFS(C$2:C$5,">"&$E$8,C$2:C$5,"<"&$E$9,$A$2:$A$5,">="&$B15,$A$2:$A$5,"<="&$C15)
E16=COUNTIFS(C$2:C$5,">"&$E$8,C$2:C$5,"<"&$E$9,$A$2:$A$5,">="&$B16,$A$2:$A$5,"<="&$C16)
F13=COUNTIFS(D$2:D$5,">"&$E$8,D$2:D$5,"<"&$E$9,$A$2:$A$5,">="&$B13,$A$2:$A$5,"<="&$C13)
F14=COUNTIFS(D$2:D$5,">"&$E$8,D$2:D$5,"<"&$E$9,$A$2:$A$5,">="&$B14,$A$2:$A$5,"<="&$C14)
F15=COUNTIFS(D$2:D$5,">"&$E$8,D$2:D$5,"<"&$E$9,$A$2:$A$5,">="&$B15,$A$2:$A$5,"<="&$C15)
F16=COUNTIFS(D$2:D$5,">"&$E$8,D$2:D$5,"<"&$E$9,$A$2:$A$5,">="&$B16,$A$2:$A$5,"<="&$C16)
 
Last edited:
Upvote 0
Another way.

ABCDEFGHIJKLMNOPQRS
Begning Date
Week
Closing Date
Total
student1
student2
student3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]4/13/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]20.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8/31/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8/10/2018[/TD]
[TD="align: right"]8/3/2018[/TD]
[TD="align: right"]7/27/2018[/TD]
[TD="align: right"]7/20/2018[/TD]
[TD="align: right"]7/13/2018[/TD]
[TD="align: right"]7/6/2018[/TD]
[TD="align: right"]6/29/2018[/TD]
[TD="align: right"]6/22/2018[/TD]
[TD="align: right"]6/15/2018[/TD]
[TD="align: right"]6/8/2018[/TD]
[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]5/25/2018[/TD]
[TD="align: right"]5/18/2018[/TD]
[TD="align: right"]5/11/2018[/TD]
[TD="align: right"]5/4/2018[/TD]
[TD="align: right"]4/27/2018[/TD]
[TD="align: right"]4/20/2018[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]218[/TD]
[TD="align: right"]239[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]282[/TD]
[TD="align: right"]303[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]346[/TD]
[TD="align: right"]367[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=B1+(B2*7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=COUNTIFS($C$5:$AG$5,">="&$B$1,$C$5:$AG$5,"<="&$B$3,C6:AG6,">80",C6:AG6,"<100")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=COUNTIFS($C$5:$AG$5,">="&$B$1,$C$5:$AG$5,"<="&$B$3,C7:AG7,">80",C7:AG7,"<100")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=COUNTIFS($C$5:$AG$5,">="&$B$1,$C$5:$AG$5,"<="&$B$3,C8:AG8,">80",C8:AG8,"<100")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Must Share your Experience ... Did it helped you.
 
Last edited:
Upvote 0
Edit: Ignore this post - see the next one.

I'm not sure where you are putting these formulas, but for the layout you posted in the original post, for row 5 it could be

=COUNTIFS(B5:INDEX(B5:BZ5,B$2),">80",B5:INDEX(B5:BZ5,B$2),"<100")

and copy down for the other rows.
Where I have used column BZ, just use any column that will always be more than the extent of your data.
 
Last edited:
Upvote 0
Actually, ignore my last post. If you are inserting cells to add the new week, you might need something like this.

Excel Workbook
ABCDE
1begin:current week
2end:3
3
410/08/20183/08/201827/07/201820/07/2018
5student187917279
6student298979092
7student38736582
8
9
10
11student12
12student23
13student30
COUNTIFS



If you then insert new cells at B4:B7, the formulas in rows 11:13 will still pick up those new cells as the "current week" for the new data added.
For example, here I have added new cells as described and entered some new data. The formulas now (automatically) reflect the results from a different set of columns.

Excel Workbook
ABCDEF
1begin:current week
2end:3
3
417/08/201810/08/20183/08/201827/07/201820/07/2018
5student18587917279
6student24098979092
7student3858736582
8
9
10
11student13
12student22
13student31
COUNTIFS
 
Upvote 0
Thank you all for the solutions. Just got home from work, so I'm gonna spend some time to go over all the answers and digest them, and I'll post feedback soon. Thanks again!
 
Upvote 0
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"]
<colgroup><col><col></colgroup><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]


I think the rest of the formulas need to be changed as well, but I couldn't figure how. So Muhammad, could you please update your solution so it works by going back in time? Thanks much!



RasGhul's solution would be difficult to implement because I have too many sheets already written in the current format, and changing them would be very time consuming.

Peter's solution is also very simple and nice, but not able to implement the begin and end dates as flexibly as Muhammad's solution.

Thanks everybody!
 
Upvote 0
Rnkhch said:
Peter's solution is also very simple and nice, but not able to implement the begin and end dates as flexibly as Muhammad's solution.
Click to expand...
I did it that way because that was what you specifically asked for. ;)
Rnkhch said:
.. 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?
Click to expand...
 
Upvote 0
Yes, thanks. After reading Mohammad's response, I realized it will be be better to have that extra flexibility. Hopefully he can send the revised solution, but if you wanna add that feature to your solution, that will be awesome too :) thanks!
 
Upvote 0
Rnkhch said:
Yes, thanks. After reading Mohammad's response, I realized it will be be better to have that extra flexibility.
Click to expand...
Doesn't that still have the problem of when you insert a new column at the left you have to manually adjust the ranges in the formulas?

See if this modification helps. If you want to go back in time instead of forward, still put your 'anchor' data in B1 but use a negative number of weeks in B2.
If you insert a new column C, my formulas in column B should still pick up that new column (if in the target date range) & also expand to the right to keep all the data within range.
I have also made it easier to change the score range using cells E1:E2

My example is counting in the green columns (backwards 4 weeks starting at the date in B1).

Excel Workbook
ABCDEFGHIJ
1Begning Date3/08/2018Min80
2Weeks (+/-)-4Max100
3Closing Date13/07/2018
4
5Total10/08/20183/08/201827/07/201820/07/201813/07/20186/07/201829/06/201822/06/2018
6student149090909090909090
7student207070707070707070
8student318736582111132153175
Count in date range
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Analysing Data According to day of the week.
Replies
5
Views
140
Asbestos_Jen
Asbestos_Jen
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
158
sijpie
sijpie
G
  • Solved
Golf Handicap Formula help
2
Replies
11
Views
550
Gulfcourse
G
J
  • Solved
Converting Month to Weeks from the current date
Replies
8
Views
603
jajatidev
J
S
  • Question Question
Fix Column Reference? Changes with External Data Refresh
Replies
1
Views
299
Scotster
S

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top