statement to extract data based on date

asmith601

New Member
Joined
Dec 5, 2017
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
Hi guys. I am a teacher working on a spreadsheet that requires the extraction of data separately for fall & spring semester. My dates are done in columns so that each row is a student. Please see me cheesy example below:

8/17/2017 12/19/2017 1/3/2018 5/24/2018

Student1 X 0 0 X
Student2 X X X X

Here is my statement:

=IF(AND(TODAY()>='Days Off'!D3,TODAY()<='Days Off'!D4),COUNTIF('Roster Engagement'!D18:CJ18,"X")+D17,IF(TODAY()>='Days Off'!D6,COUNTIF('Roster Engagement'!CK18:II18,"X")+D17))

I need to sum up the absences for each semester i.e., student1 has 1 for each. and student2 has 2 for each.

The "Days Off" worksheet is where i store holidays, state & end dates; the "Roster Engagement" is where I store the daily info above; ignore the D17. This statement appears to return the info needed, but I think it might be a sloppy way of doing it. Are there any ideas on a more robust statement?

Thanks in advance!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
04/12/201705/12/201706/12/201707/12/201708/12/201707/01/201808/01/201809/01/201810/01/201811/01/2018
fred00000
bill0
my more cheesier example has semesters lasting only 5 days4th to 8th december and 7th to 11th january
clearlyfred was absent twice in the first and 3 times in the second
bill was absent only one day in the first semestre
04/12/201707/01/2018
08/12/201711/01/2018
fred23
bill10
formula returning 2
=SUMPRODUCT(($B$2:$K$2>=D$15)*($B$2:$K$2<=D$16)*($B3:$K3<>""))
dragged across and down
note we count all cells that are not blank within the date constraints

<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

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
Top