COUNT BASED ON DATES WITH CRITERIA

[Unlucky]

Before submitting this message I did review two similar message board entries, but I wasn't able to figure out how to make the formulas work for me. (I reviewed Count 30, 60 and over 90 days based on Name in Column "A" and Countifs or sumproduct - date range and multiple criteria )

Basically, I want to count the number of occurrences based on the past 1, 3, 6, 9, and 12 months. The criteria is variable, based on a drop down and the date, and then a total for the same time period without counting duplicates in the total. The two variables cells are B1 (Drop Down), and D7 and D15 (Today()). The drop down list is Column H.

The counts are based on the issue dates in Column M of the source table, based on the program in Column O of the source table.

Here is the report table:

TEST LOG FOR FORMULAS.xlsx
	B	C	D	E	F	G	H
1	ABC	OPEN DOCUMENTS IN THE PAST:					PROGRAM
2	1 MONTH	3 MONTHS	6 MONTHS	9 MONTHS	12 MONTHS		ABC
3							CDR
4							CRF
5							ILF
6	ABC	TOTAL	TODAY				ILS
7	PAST YEAR		22-Dec-21				PCF
8							PRT
9	ALL	OPEN DOCUMENTS IN THE PAST:					QMF
10	1 MONTH	3 MONTHS	6 MONTHS	9 MONTHS	12 MONTHS		WTF
11							WWW
12
13
14	ALL	TOTAL	TODAY		DATES
15	PAST YEAR		22-Dec-21		1
16					3
17					6
18					9
19					12
FORMULA TEST

Cell Formulas
Range	Formula
B6	B6	=B1
D7,D15	D7	=TODAY()

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=$H$2:$H$11

 

[Unlucky]

Here is the source table, located on another worksheet:

TEST LOG FOR FORMULAS.xlsx
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
24	SOURCE DATA TABLE
25	NUMBER		LEVEL	ISSUED	CAP DUE	PROGRAM		ISSUED BY			STATUS	COMMENTS
26	2938120200005A		I	7-Jan-21	17-Jan-21	ABC		Tom			CLOSED	Loose hardware
27	2938120200006A		I	12-Jan-21	22-Jan-21	ABC		Charlie			CLOSED	Installed backwards
28	2938120200007A		I	13-Jan-21	23-Jan-21	CDR		William			CLOSED	Loose hardware
29	2938120200008A		I	21-Jan-21	31-Jan-21	QMF		William			CLOSED	Missing hardware
30	2938120200008B		II	3-Feb-21	13-Feb-21	ABC		Bob			OPEN	Installed upside down
31	2938120200008C		III	4-Feb-21	14-Feb-21	CRF		Tom			OPEN	Missing paint
32	2938120200009A		I	13-Feb-21	23-Feb-21	CDR		Greg			OPEN	Loose hardware
33	2938120200010A		I	8-Mar-21	18-Mar-21	QMF		Tom			CLOSED	Loose hardware
34	2938120200011A		I	9-Mar-21	19-Mar-21	WWW		William			CLOSED	Installed upside down
35	2938120200011B		II	12-May-21	22-May-21	WTF		Charlie			CLOSED	Installed backwards
36	2938120200012A		I	14-Jun-21	24-Jun-21	CDR		Bob			OPEN	Missing paint
37	2938120200013A		I	7-Jul-21	17-Jul-21	ABC		Bob			OPEN	Operator error
38	2938120200014B		II	10-Jul-21	20-Jul-21	ABC		Bob			OPEN	Failed test
39	2938120200015A		I	22-Sep-21	2-Oct-21	WTF		Tom			OPEN	Loose hardware
40	2938120200016A		I	22-Sep-21	2-Oct-21	WWW		William			CLOSED	Missing paint
41	2938120200016C		III	23-Sep-21	3-Oct-21	ILS		Charlie			OPEN	Installed backwards
42	2938120200017A		I	4-Oct-21	14-Oct-21	PRT		Charlie			CLOSED	Loose hardware
43	2938120200018A		I	5-Oct-21	15-Oct-21	ABC		Bob			OPEN	Loose hardware
44	2938120200019A		I	12-Oct-21	22-Oct-21	CDR		Greg			CLOSED	Installed backwards
45	2938120200020A		I	14-Oct-21	24-Oct-21	CRF		Tom			CLOSED	Loose hardware
46	2938120200021A		I	3-Nov-21	13-Nov-21	CRF		William			CLOSED	Missing hardware
47	2938120200022B		II	6-Nov-21	16-Nov-21	CRF		Bob			CLOSED	Installed upside down
48	2938120200023A		I	6-Nov-21	16-Nov-21	WWW		Bob			CLOSED	Missing paint
49	2938120200023B		II	7-Nov-21	17-Nov-21	ABC		William			OPEN	Loose hardware
50	2938120200024A		I	28-Nov-21	8-Dec-21	ILF		Greg			OPEN	Loose hardware
51	2938120200025A		I	29-Nov-21	9-Dec-21	WTF		Tom			OPEN	Installed upside down
52	2938120200026A		I	8-Dec-21	18-Dec-21	ABC		Tom			OPEN	Installed backwards
53	2938120200027A		I	10-Dec-21	20-Dec-21	ABC		Tom			OPEN	Missing paint
54	2938120200027C		III	15-Dec-21	25-Dec-21	ABC		Tom			OPEN	Missing hardware
55	2938120200028A		I	22-Dec-21	1-Jan-22	WTF		Greg			OPEN	Installed upside down
56	2938120200029A		I	30-Dec-21	9-Jan-22	WWW		William			OPEN	Missing paint
57	2938120200030B		II	30-Dec-21	9-Jan-22	ILS		Bob			OPEN	Loose hardware
58	2938120200030C		III	31-Dec-21	10-Jan-22	PCF		Tom			OPEN	Loose hardware
FORMULA TEST

Cell Formulas
Range	Formula
N26:N58	N26	=M26+10

 

[Unlucky]

I wanted to say thank you to al the experts for the previous assistance you provided this past year. You were a tremendous help with my work project.

That being said, I made a post December 22, 2021 that went unanswered. Based on the sheer volume and complexity of posts on the message board, that is not unreasonable. Also, I completely understand that I am not the only one that posts with their own set of unique problems, and I completely understand that the experts are all volunteers, assisting as they see fit. I have only the highest regard for those that take their own time to help those with less knowledgeable, like me. Fortunately, most of the time I can either figure it out on my own, or stumble onto a post where someone has / had a similar problem, either on Mr. Excel or another website. I did look over older posts on the subject prior to posting, but I couldn't figure out the correct formulas, either arrayed or non-arrayed.

Nevertheless, I am still in dire need of assistance, and for anyone that can help, I will be eternally grateful. This is the last piece of the puzzle I'm trying to put together.

So again, I am very thankful for all the assistance the excel experts provided since I joined in 2014.