Counting Number of Documents in a Column based on month and other criteria

[MooseManJKT]

Hi everyone

First of all, i'd like to say i'm happy to be part of this forum. Glad to meet you all, and I hope I can be as helpful to others here as much as I need assistance (i will try hehe).

Note:
i'm referencing rows and columns on this post to the file I made available in this link via google drive share.
Please feel free to download the .xlsx file for clarity (I hope this is allowed here ). I've also uploaded the excel screenshot just in case that is needed.
I can't use Mini-Sheet as this is a corporate laptop that requires admin rights to install anything.


On the the issue at hand:
I've made an "Engineering Document Submission" control sheet to help me track the various engineering documents produced and submitted to our customer. I made it this way so I can use the same template for multiple projects. The data (K8:Y9 and K11:Y12) will be used as part of a weighted progress S-Curve.


What I have so far:
Cells K8:Y8 counts the documents within the range G7:G36 that are planned to be 1st submitted within the month (using the months in K7:Y7) that are "Key Documents" (using Column D).
The formula I used is =COUNTIFS($G$7:$G$36;">="&K$7;$G$7:$G$36;"<"&EDATE(K$7;1);$D$7:$D$36;"Y")

This gives me the correct count across all months for the Key Documents-1st Approval counts.
I use the same formula for Cells K11:Y11 to count the documents same as above, but are "Non-Key Documents" (using Column E) as so
=COUNTIFS($G$7:$G$36;">="&K$10;$G$7:$G$36;"<"&EDATE(K$10;1);$E$7:$E$36;"N")


What I need next:
For the next step (approvals), there are documents that are submitted with a "For Information" status, which means that the document does not require approval from the customer.

So for cells K9:Y9, I need to count for documents within the range H7:H36 that are planned to be approved within the month (using the months in K7:Y7) that are "Key Documents" (using Column D), but also count documents either with N/A in the counted colum , or documents with FI in Column F.


What can't work:
I tried fiddling around with multiple forms of =COUNTIFS, but i don't think it works with OR criteria (i read that COUNTIFS is basically exclusively AND operator). I've also tried SUMPRODUCT or MATCH, but i think i'm using the wrong methids.


Any help from any masters in this forum is greatly appreciated. Been trying to figure this out for days.

Sincerely,
Moose in Jakarta, Indonesia

 

[MooseManJKT]

Finally managed to install XL2BB! Really nifty! Below is the mini-sheet:

Document Status Count.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1		ENGINEERING DOCUMENTS SUBMISSION PLAN
2
3			COPY PASTE from VDRL	INPUT "Y" or "N"			INPUT dates as planned (place "N/A" if FOR INFO)				PLANNING
4
5		No.	Title	Key Doc?			PLANNED				Submission Period
6				Y	N	Info?	1st Submission	Approval			2022			2023												2024
7		1		Y			4-Nov-22	20-Nov-22		Key Documents	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep
8		2		Y		FI	4-Nov-22	N/A		1st Submission	0	7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9		3		Y		FI	4-Nov-22	N/A		Approval	0	3	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10		4		Y		FI	4-Nov-22	N/A		Non-Key	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep
11		5		Y			11-Nov-22	27-Nov-22		1st Submission	0	2	3	1	0	2	2	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0
12		6		Y			11-Nov-22	27-Nov-22		Approval	0	0	5	1	0	2	0	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13		7		Y			16-Nov-22	2-Dec-22
14		8			N		16-Nov-22	2-Dec-22		This is what I'm trying	Is a KEY DOC
15		9			N		16-Nov-22	2-Dec-22		to make happen for	AND
16		10			N		12-Dec-22	28-Dec-22		Key Doc-Approval section	Status is "FI" AND 1ST SUBMISSION PLAN is within MONTH
17		11			N		9-Jan-23	25-Jan-23		above (K9:Y9)	OR
18		12			N		12-Dec-22	28-Dec-22			APPROVAL PLAN is within MONTH
19		13			N		12-Dec-22	28-Dec-22
20		14			N		6-Mar-23	22-Mar-23
21		15			N		17-Mar-23	2-Apr-23
22		16			N		30-Apr-23	16-May-23
23		17			N		30-Apr-23	16-May-23
24		18			N	FI	30-Jul-23	N/A
25		19			N	FI	15-Sep-23	N/A
26		20
27		31
28		32
29		33
30		34
31		35
32		36
33		37
34		38
35		39
36		40
37			Totals	7	12	5
Eng-Doc List

Cell Formulas
Range	Formula
H7,H11:H23	H7	=G7+16
L7:AH7,L10:AH10	L7	=K7+31
K8:AH8	L8	=COUNTIFS($G$7:$G$36,">="&L$7,$G$7:$G$36,"<"&EDATE(L$7,1),$D$7:$D$36,"Y")
L9:AH9	L9	=COUNTIFS($H$7:$H$36,">="&L$7,$H$7:$H$36,"<"&EDATE(L$7,1),$D$7:$D$36,"Y")
K9	K9	=COUNTIFS($H$7:$H$36,">="&K$7,$H$7:$H$36,"<"&EDATE(K$7,1),$D$7:$D$36,"Y",H7:H36,"N/A")
K11:AH11	K11	=COUNTIFS($G$7:$G$36,">="&K$10,$G$7:$G$36,"<"&EDATE(K$10,1),$E$7:$E$36,"N")
K12:AH12	K12	=COUNTIFS($H$7:$H$36,">="&K$10,$H$7:$H$36,"<"&EDATE(K$10,1),$E$7:$E$36,"N")
D37:F37	D37	=COUNTA(D7:D36)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K7:AH7,K10:AH10	Expression	=IF(K$7=#REF!;TRUE;FALSE)	text	NO