BOT's #4: Countif text exists in a column/range and date is between two dates

My previous BOT would be incomplete without this formula. A use case in this scenario is in marking classroom or course attendance for individual learners during a certain period. The period here could be within an hour, a day, a week, or a month depending on whichever suits your need.

Tip:?Depending on your class size, you can either tweak the formula to mark one being present or absent.

The formula when absent

=ARRAYFORMULA(IF(LEN(?I2:I?),IF(TODAY()>=?K2:K,(NETWORKDAYS(?J2:J,?K2:K)-COUNTIFS(?Attendance!AC:AC,?">="&?J2:J,?Attendance!AC:AC,?"<="&?K2:K,?Attendance!AB:AB,?"*"&?I2:I&?"*")), (NETWORKDAYS(?J2:J,TODAY()))-COUNTIFS(?Attendance!AC:AC,?">="&?J2:J,?Attendance!AC:AC,?"<="&?K2:K,?Attendance!AB:AB,?"*"&?I2:I&?"*")),?""))        

Breaking down the formula

• IF(LEN(?I2:I): We are testing if there is any character or a string in the column?I2:I
• IF(TODAY()>=K2:K: We are testing if the date today is greater than or equal to the?End Date?values in the column?K2:K
• NETWORKDAYS(?J2:J,?K2:K): Formula to return the number of workdays between?Start Date?values in the column?J2:J?and?End Date?values in the column?K2:K
• COUNTIFS(?Attendance!AC:AC,?">="&?J2:J,?Attendance!AC:AC,?"<="&?K2:K,?Attendance!AB:AB,?"*"&?I2:I&?"*"): Countifs with wildcards?"*"&?I2:I&?"*"?for?Full Names?text in column?Attendance!AB:AB?where?Start Date?values in the column?J2:J?is greater than or equal to datevalue in column?Attendance!AC:AC?and?End Date?values in the column?K2:K?is less than or equal to datevalue in column?Attendance!AC:AC
• NETWORKDAYS(?J2:J, TODAY()): Formula to return the number of workdays between?Start Date?values in the column?J2:J?and the date today

Note:?For a scenario where you want to mark present do not use the NETWORKDAYS formula.

Testing this formula

1. Start by scheduling your preferred class on the?Schedule a class?form
2. Enter your details and those of your preferred class on the?Register for training?form
3. Navigate to the main bar on the?Students Attendance?spreadsheet and run the designated script under Update Form menu for your subject. This ensures that the names are auto-populated in corresponding form answer options.
4. Mark attendance?and check the change in your results under the?Students_Attendance_Consolidated?tab on the spreadsheet in step 3.

To conclude this formula is nested in the ARRAYFORMULA which means there is no need to drag the same formula across the column. The one formula does the job for the entire column. Good luck with trying it out. Cheers!

About the author

Rogers is a data and emerging tech enthusiast. He is the Co-founder and VP of Operations at OurPass.

OurPass is a global neobank providing businesses of all sizes with access to every banking, payment, and business management tool they need to start, grow and scale their businesses.

Our mission is to create a borderless world of successful businesses and we are constantly building new tools to ensure that whatever stage a business is in, they have access to every tool they need to thrive, all in one place.

Founded in 2021 as a one-click checkout company, we made a pivot in July 2022 when we saw that beyond offering a niche service, we could provide end-to-end solutions that helped entrepreneurs grow every aspect of their businesses.

In just a few months since our transition, we have become a leading business bank serving thousands of customers including some of the biggest retail outlets in Nigeria such as Spar, Shoprite, and Eat N Go (parent company of Dominos, Cold Stone Creamery, and Pinkberry), processing about a million transactions monthly.

OurPass also holds a Microfinance Banking License from the Central Bank of Nigeria.

To unlock limitless smart business banking that OurPass app offers, download the app from the?App Store?or?Google Play Store, create an account, and join thousands of businesses growing with us.

Connect with us, join our vibrant community on?Facebook,?Twitter,?Instagram,?LinkedIn,?and?YouTube, and stay up to date with the latest information about our services.