statement to extract data based on date

asmith601

New Member
Joined
Dec 5, 2017
Messages
20
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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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>
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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