Mix of info in a column that needs to be specific in a given month, but matches the final criteria for a total.

[StephG]

Hi all, sorry if this has come up before, I don't know what to look for so I will try for some help.
in my spreadsheet, i have tried a few things but stumped on the how to get it to work. I have added the formulas I have tried, and the answer I am looking for.
I am hoping this is something you can help me with.
Column D is the month and will update as it gets added to.
Column C is the department if you like, that i need to count, that has raised however many Behavioural Or Operational.
I cant work out how to get the formulas to do what I need them to do.
P3 is a total of the month
P4 & P5 are the formulas I found and tried but dont work.
Any help here is much appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Timestamp	Who is reporting	Department where incident has occurred.	Date	Time of incident approx.	Brief description of incident	Have you witnessed incident?	Has the departmental manager/supervisor been informed?	What action did you take?	What are you reporting?		What are you reporting?	Type
2			Logistics - Yard, off site	21/10/2024									Behavioural Observation			Oct
3			Logistics - Yard, off site	22/10/2024									Behavioural Observation			9
4			Warehouse - Ambient, Freezers	17/10/2024									Behavioural Observation		BSO	09/01/1900	Should=6
5			Production - Ground floor	22/10/2024									Operational Observation		OPS	0	should =1
6			Warehouse - Ambient, Freezers	23/10/2024									Operational Observation		BSO
7			Offices	24/10/2024									Behavioural Observation		OPS
8			Production - Ground floor	24/10/2024									Behavioural Observation
9			Production - Ground floor	09/10/2024									Behavioural Observation
10			Production - Ground floor	09/10/2024									Behavioural Observation
Sheet1

Cell Formulas
Range	Formula
P3	P3	=COUNTIFS(D$2:$D10,">=01/10/2024",D$2:$D10,"<=31/10/2024")
P4	P4	=SUM(COUNTIFS($D$2:$D$10,">=01/10/2024",$D$2:$D$10,"<=31/10/2024")+COUNTIFS($C$2:$C$10,"*ware*",$C$2:$C$10,"*log*",$M$2:$M$10,"*behav*"))
P5	P5	=SUM(COUNTIFS($C$2:$C$10,">=01/10/2024",$C$2:$C$10,"<=31/10/2024",$D$2:$D$10,"*ware*",$D$2:$D$10,"*log*",$M$2:$M$10,"*oper*"))

 

[Fluff]

Hi & welcome to MrExcel.
For BSO try

=SUM(COUNTIFS($D$2:$D$10,">=01/10/2024",$D$2:$D$10,"<=31/10/2024",$C$2:$C$10,{"*ware*","*log*"},$M$2:$M$10,"*behav*"))

but there are only 3 rows that match the criteria, not 6

 

[StephG]

Hi & welcome to MrExcel.
For BSO try

=SUM(COUNTIFS($D$2:$D$10,">=01/10/2024",$D$2:$D$10,"<=31/10/2024",$C$2:$C$10,{"*ware*","*log*"},$M$2:$M$10,"*behav*"))

but there are only 3 rows that match the criteria, not 6

Thank you so much for your help!!! You have made my day! You are right, i was getting bogged down in the colours! This is great thank you x

 

[MARZIOTULLIO]

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Date	Time of incident approx.	Brief description of incident	Have you witnessed incident?	Has the departmental manager/supervisor been informed?	What action did you take?	What are you reporting?		What are you reporting?	Type
2	10/21/2024									Behavioural Observation			Oct	9
3	10/22/2024									Behavioural Observation
4	10/17/2024									Behavioural Observation		Behavioural Observation	7
5	10/22/2024									Operational Observation		Operational Observation	2
6	10/23/2024									Operational Observation
7	10/24/2024									Behavioural Observation
8	10/25/2024									Behavioural Observation
9	10/9/2024									Behavioural Observation
10	10/9/2024									Behavioural Observation
Sheet1

Cell Formulas
Range	Formula
Q2	Q2	=SUMPRODUCT((MONTH(D2:D10)=MONTH(P2&0))*(ISNUMBER(MATCH($M$2:$M$10,O4:O5,0))))
P4:P10	P4	=IF(O4<>"",SUMPRODUCT((MONTH($D$2:$D$10)=MONTH($P$2&0))*($M$2:$M$10=O4)),"")

 

[Fluff]

You're welcome & thanks for the feedback.