Result of COUNTIFS in a specified date range and adding conditional criteria

[isabel1217]

Appreciate very much if you could modify my formula to insert another criteria to count number of individuals attends on Mon, Wed, Tue, Thu, & Fri within a required date range specified.
I was able to count how many students attended from K29:BN29 (13/09/2015 - 07/11/2015) but I would like to count how many student attends only from 13/09/2015 to 15/10/2015.

Date Range K28:BN28
Days Range (Mon, Tue, Wed, Thu, Fri, Sat, Sun) :K26:BN26
Individual Range: K29:BN29 (Included here is Student, Teachers and Technicians)
Required Starting Date: C29
Required End Date: E29
Student : H27
Teacher : G27

Example: 13/09/2015 - 07/11/2015
Required Starting Date: 13/09/2015
Required End Date: 15/10/2015

1. This is my formula that needs modifying to count the number of students (H27) from 13/09/15 (C29) to 15/10/2015 (E29) only for Mondays, Tuesdays, Wednesday, Thursdays and Fridays (K29:BN29)

=COUNTIFS(K26:BN26,"MON",K29:BN29,H27)+COUNTIFS(K26:BN26,"TUE",K29:BN29,H27)+COUNTIFS(K26:BN26,"WED",K29:BN29,H27)+COUNTIFS(K26:BN26,"THU",K29:BN29,H27)+COUNTIFS(K26:BN26,"FRI",K29:BN29,H27)


2. If I want to count the number of students (H27) and teachers (G27) from 13/09/15 to 15/10/2015. What would be the formula look like?


3. My Sunday Formula looks like this. I prefer to make a separate formula for Sunday for other purposes as Sundays have different applied penalty. I would like to modify this to find number of students attends Sundays from required starting date: 13/09/2015 to required end date: 15/10/2015 only.

=COUNTIFS(K26:BN26,"SUN",K29:BN29,H27)

I would be grateful if someone could look into this please. This is the only thing that I needed to complete the project that I am making.

Thank you very much.

 

[Chrisdontm]

I would like to simplify it more, but...

I am assuming that the word " Student " is in H27, and the words "Student", "Teacher", and, "Technician" are the only words in K29:BN29
If so this should count the students only within whatever dates you put inside C29 & E29...

Try this;


=COUNTIFS(K26:BN26,"MON",K29:BN29,H27,K28:BN28,">="&C29,K28:BN28,"<="&E29)+COUNTIFS(K26:BN26,"TUE",K29:BN29,H27,K28:BN28,">="&C29,K28:BN28,"<="&E29)+COUNTIFS(K26:BN26,"WED",K29:BN29,H27,K28:BN28,">="&C29,K28:BN28,"<="&E29)+COUNTIFS(K26:BN26,"THU",K29:BN29,H27,K28:BN28,">="&C29,K28:BN28,"<="&E29)+COUNTIFS(K26:BN26,"FRI",K29:BN29,H27,K28:BN28,">="&C29,K28:BN28,"<="&E29)

And as for Sunday;

=COUNTIFS(K26:BN26,"SUN",K29:BN29,H27,K28:BN28,">="&C29,K28:BN28,"<="&E29)

You should be able to tweak it from here if you need to...

 

[isabel1217]

Hello Chrisdontm...thank you very much. I really appreciated this. This formula followed through my weekend and in my dreams. You're the best! Will try this when I get to work!. I'm really grateful! Thank you! Thank you!

Will let you know soon!!!

 

[isabel1217]

Hi Chrisdontm..your formula works very well! Thank you very much.

 

[Chrisdontm]

Your welcome, I am glad it worked for you...