Counting Theory and Practical Periods with Conditions

[hotbird]

There are two sheets in my excel file, named 1) Basic_Info and 2) Sep
1) In this Sep sheet Mech-246 and others represents the subject name.
2) Th stands for Theroy period and Pr stands for Practical period.
3) First four periods (1,2,3,4,) are paid at half the rate and the last four periods (5,6,7,8) are paid at full rates except Friday.
4) On friday all the periods are paid at full rate.
5) There are only six periods taught on friday ( 3 to 8).
------------------------
6) I want to count number of theory periods (half and full paid separately) at the end of the sheet.
7) Similary i also want to count number of practical periods paid at full and half rate separately.
8) I have used countif function but it does not work.
9) countifs function does not give output.
10) i have also tried sumproduct, if and weekday functions but doesnot know how to write these functions correctly.
11) In this sheet Full rate theroy periods are 17 and half rate theroy periods are 9.
12) Similary full rate practical periods are 38 and half rate practical periods are 28.

Note:
1) Cell A8 is linked to the other sheet and cell A9 thorugh A37 contain formulas A8+1, A9+1 etc to get dates.
2) Similary cell B8 gets function weekday to get day from dates in A8, A9 etc.

Help will be appreciated. Thanks

Basic_Info Sheet:

Example.xlsx
	A	B	C	D	E
1	Basic Info for Second Shift Bills
2	Teacher's	Account Information
3	Name	Account No.	Bank Name (in short)	Branch
4	ABCD	0	XYZ	NYC
5
6	Month with Year	Rate		Bill Printing Date
7		Theory	Practical
8	Sep-2022
9	Oct-2022
10	Nov-2022
11	Dec-2022
12	Jan-2023
13	Feb-2023
14	Mar-2023
15	Apr-2023
16	May-2023
17	Jun-2023
18	Jul-2023
19
Basic_Info

Sep Sheet:

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	College Name
2	Attendance Proforma
3
4	Name of Teacher :			ABCD				Month :	September - 2022
5
6	Date	Day	Periods								Verified by
7			1	2	3	4	5	6	7	8
8	1	Thu				Mech-372 Th		Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
9	2	Fri	X	X	Mech-246 Th (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)
10	3	Sat	Mech-372 Pr	Mech-372 Pr	Mech-372 Pr
11	4	Sun
12	5	Mon		Mech-363 Pr	Mech-363 Pr	Mech-363 Pr	Mech-363 Th	Mech-363 Th	Mech-372 Th
13	6	Tue				Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
14	7	Wed	Mech-246 Th (A)
15	8	Thu				Mech-372 Th		Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
16	9	Fri	X	X	Mech-246 Th (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)
17	10	Sat	Mech-372 Pr	Mech-372 Pr	Mech-372 Pr
18	11	Sun
19	12	Mon		Mech-363 Pr	Mech-363 Pr	Mech-363 Pr	Mech-363 Th	Mech-363 Th	Mech-372 Th
20	13	Tue				Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
21	14	Wed	Mech-246 Th (A)
22	15	Thu				Mech-372 Th		Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
23	16	Fri	X	X	Mech-246 Th (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)
24	17	Sat	Mech-372 Pr	Mech-372 Pr	Mech-372 Pr
25	18	Sun
26	19	Mon		Mech-363 Pr	Mech-363 Pr	Mech-363 Pr	Mech-363 Th	Mech-363 Th	Mech-372 Th
27	20	Tue				Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
28	21	Wed	Mech-246 Th (A)
29	22	Thu				Mech-372 Th		Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
30	23	Fri	X	X	Mech-246 Th (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)
31	24	Sat	Mech-372 Pr	Mech-372 Pr	Mech-372 Pr
32	25	Sun
33	26	Mon		Mech-363 Pr	Mech-363 Pr	Mech-363 Pr	Mech-363 Th	Mech-363 Th	Mech-372 Th
34	27	Tue				Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
35	28	Wed	Mech-246 Th (A)
36	29	Thu				Mech-372 Th		Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)	Mech-246 M/S Pr (A)
37	30	Fri	X	X	Mech-246 Th (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)	Mech-246 M/S Pr (B)
38
39
40			Periods:	Theory	Practical
41			Full Rate	12	33
42			Half Rate	14	33
43			Total	26	66		Signature of Teacher :
44
45
Sep

Cell Formulas
Range	Formula
D4	D4	=Basic_Info!A4
I4	I4	=Basic_Info!A8
A8	A8	=Basic_Info!A8
B8:B37	B8	=WEEKDAY(A8)
A9:A37	A9	=A8+1
D41	D41	=COUNTIF(G8:J37, "*Th*")
E41	E41	=COUNTIF(G8:J37, "*Pr*")
D42	D42	=COUNTIF(C8:F37, "*Th*")
E42	E42	=COUNTIF(C8:F37, "*Pr*")
D43:E43	D43	=SUM(D41:D42)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT(((B8:B37=6)*(ISNUMBER(SEARCH("Th",C8:F37))))+(ISNUMBER(SEARCH("Th",G8:J37))))

and

=SUMPRODUCT((B8:B37<>6)*(ISNUMBER(SEARCH("Th",C8:F37))))

 

[hotbird]

@Fluff Thanks for welcoming.
The formula is working fine. I am very happy with it.
Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[hotbird]

Hello;
Now i want some change in counting. All above conditions are same except condition 3. New condition is as;
3) First two periods (1,2) are paid at half the rate and the last six periods (3,4,5,6,7,8) are paid at full rates except Friday.

 

[Fluff]

How about

=SUMPRODUCT(((B8:B37=6)*(ISNUMBER(SEARCH("Th",C8:D37)))))+SUMPRODUCT(--(ISNUMBER(SEARCH("Th",E8:J37))))

and

=SUMPRODUCT((B8:B37<>6)*(ISNUMBER(SEARCH("Th",C8:D37))))

 

[hotbird]

Thanks @Fluff. Working fine.

 

[Fluff]

You're welcome & thanks for the feedback.