Sum total filtered data only, with multiple criteria

[1988craig]

Hello Excel Gurus,

Any assistance with this is greatly appreciated.
I am trying to obtain the total of column L, when the data is filtered by column R.
using the criteria of anything that contains the letter "*E*", in column E, anything "ongoing", or "complete" in column O.

look forward to any assistance thank you!

TEST 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1						MMF Planned WO's	EEI Planned WO's		EEI Man Hrs Planned works			planned Man Hrs
2						244	275		700.5			2134.5			281
3	Planned Start Date (JDE)	Actual Execution Date	LOPA Due Date	Work Order Number	LEAD Craft	Operation Craft	Function Location	Work Order Description	E	Operation Number #	Craft Number	Labour Hours	Duration	WO Type	Status	Non Completion Reason	Actioned Y/N	Month	trade
4	Jul-2023	Mon 10 Jul		1515018	MMF	MMF		Replace Broken Lower Shaft			2	14.0	7.0		Ongoing		Yes	Jul	M
5	Jul-2023	Mon 10 Jul		1531982	MMF	MMF		PSV INSPECTION RETEST			1	2.0	2.0		Ongoing		Yes	Jul	M
6	Jul-2023	Mon 10 Jul		1516676	MMF	MMF		PSV INSPECTION RETEST			2	4.0	2.0		Ongoing		Yes	Jul	M
7	Jul-2023	Mon 10 Jul		1539989	MMF	MMF		PSV INSPECTION RETEST			2	4.0	2.0		Ongoing		Yes	Jul	M
8	Jul-2023	Mon 10 Jul		1538665	MMF	MMF		Oil still PSV passing			#DIV/0!	0.0	0.0		Ongoing		Yes	Jul	M
9	Jul-2023	Mon 10 Jul		1512778	MMF	MMF		PSV INSPECTION RETEST			2	4.0	2.0		Ongoing		Yes	Jul	M
10	Jul-2023	Mon 10 Jul		1524792	MMF	MMF		1Y Maintenance & Oil Change			1	6.0	6.0		Complete			Jul	M
11	Jul-2023	Mon 10 Jul		1524840	EAT	EAT		1W Calibrate Analyser			1	2.0	2.0		Complete			Jul	E
12	Jul-2023	Mon 10 Jul		1523793	EEI	EEI		2M Sump Electrical Inspection			1	2.0	2.0		Complete			Jul	E
13	Jul-2023	Mon 10 Jul	LOP	1524818	EEI	EEI		2W Calibrate pH Transmitter			1	2.0	2.0		Complete			Jul	E
14	Jul-2023	Mon 10 Jul	LOP	1525384	EEI	EEI		1M Calibrate Analyser			1	2.0	2.0		Complete			Jul	E
15	Jul-2023	Mon 10 Jul		1470128	EEI	EEI		RepairTemp reading IOP			1	4.0	4.0		Pushout	production	Yes	Jul	E
16	Jul-2023	Mon 10 Jul	ENV	1525404	EEI	EEI		2W Calibrate pH Transmitter			1	2.0	2.0		Complete			Jul	E
17	Jul-2023	Mon 10 Jul	ENV	1525405	EEI	EEI		2W Calibrate Condy Transmitter			1	2.0	2.0		Complete			Jul	E
18	Jul-2023	Mon 10 Jul	LEN	1524099	EEI	EEI		1W Check Analyser			1	1.0	1.0		Complete			Jul	E
19	Jul-2023	Mon 10 Jul	LEN	1524100	EEI	EEI		1W Check Analyser			1	1.0	1.0		Complete			Jul	E
20	Jul-2023	Mon 10 Jul	ENV	1524104	EEI	EEI		1W Check Analyser			1	2.0	2.0		Complete			Jul	E
21	Jul-2023	Mon 10 Jul	LEN	1524837	EEI	EEI		1W Check Analyser			1	1.0	1.0		Complete			Jul	E
22	Jul-2023	Mon 10 Jul	LEN	1524838	EEI	EEI		1W Check Analyser			1	1.0	1.0		Complete			Jul	E
23	Jul-2023	Mon 10 Jul		1524112	EEI	EEI		2W Calibrate Analyser			1	2.0	2.0		Complete			Jul	E
24	Jul-2023	Mon 10 Jul	ENV	1524839	EEI	EEI		1W Check Analyser			1	2.0	2.0		Complete			Jul	E
25	Jul-2023	Mon 10 Jul	LEN	1524085	EEI	EEI		1W Check Analyser			1	1.0	1.0		Complete			Jul	E
26	Jul-2023	Mon 10 Jul	LEN	1524095	EEI	EEI		1W Check Analyser			1	1.0	1.0		Complete			Jul	E
27	Jul-2023	Mon 10 Jul	ENV	1524091	EEI	EEI		1W Check Analyser			1	2.0	2.0		Complete			Jul	E
28	Jul-2023	Mon 10 Jul		1524824	EEI	EEI		1W Check Analyser			1	2.0	2.0		Complete			Jul	E
29	Jul-2023	Mon 10 Jul		1324419	EIF	EIF		Major Motor Inspection			1	2.5	2.5		Complete			Jul	E
30	Jul-2023	Mon 10 Jul		1498140	EIF	EIF		Calibrate Level TX			1	3.0	3.0		Complete			Jul	E
31	Jul-2023	Mon 10 Jul		1498141	EIF	EIF		Calibrate Level TX			1	3.0	3.0		Complete			Jul	E
32	Jul-2023	Mon 10 Jul		1518690	EIF	EIF		1Y Calibrate Tx 1161TIT0655			1	4.0	4.0		Complete			Jul	E
33	Jul-2023	Mon 10 Jul		1534555	EEI	EEI		Replace the exisiting analyser			1	8.0	8.0		Pushout	Engineering	Yes	Jul	E
34	Jul-2023	Mon 10 Jul	5S	1524808	MTA	MTA		1W Maintain Stores Area			1	4.0	4.0		Complete			Jul	M
35	Jul-2023	Mon 10 Jul		1524807	MTA	MTA		3M Oil Sampling C Analysis			1	6.0	6.0		Complete			Jul	M
36	Jul-2023	Mon 10 Jul		1524090	MMF	MMF		6M Inspect Pump Cooler Fins			1	1.0	1.0		Complete			Jul	M
37	Jul-2023	Mon 10 Jul		1521119	MMF	MMF		6M Inspect Hose Fitting			1	4.0	4.0		Complete			Jul	M
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4:E23363,ROW(E4:E23363)-MIN(ROW(E4:E23363)),,1)),ISNUMBER(SEARCH("*M*",E4:E23363))+0)
G2	G2	=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4:E23363,ROW(E4:E23363)-MIN(ROW(E4:E23363)),,1)),ISNUMBER(SEARCH("*E*",E4:E23363))+0)
I2	I2	=SUMPRODUCT(SUBTOTAL(9,OFFSET(L4:L23363,ROW(L4:L23363)-MIN(ROW(L4:L23363)),,1)),--(E4:E23363="EEI"))
L2	L2	=SUBTOTAL(9,L3:L23363)
O2	O2	=COUNTIFS(O4:O23363,"complete")+COUNTIF(O4:O23363,"ongoing")
R4:R37	R4	=TEXT(A4,"MMM")
S4:S37	S4	=VLOOKUP(E4,$V$4:$W$46,2,FALSE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B521	Cell Value	<TODAY()	text	YES
B4:B521	Dates Occurring	today	text	YES
B4:B521	Dates Occurring	tomorrow	text	YES
B4:B521	Cell Value	="today"	text	YES

Cells with Data Validation
Cell	Allow	Criteria
O4:O37	List	Complete,Ongoing,Pushout

 

[1988craig]

Not sure if what I am requesting is possible, if anyone could advise that would be greatly appreciated, thank you.