Count days between date range based on additional critiera

[Fildes]

My first post - I see some amazing formulas here.

I feel like I am so close to achieving an outcome. I would like to acknowledge StarLux for getting me so close -

This formauila is currrenlty in cells G8 to G12 - =IF(COUNTIFS($B$3:$B$40,">="&$F$5,$B$3:$B$40,"<="&$G$5)=0,0,SUMPRODUCT((($D$3:$D$40=F8))/COUNTIFS($D$3:$D$40,$D$3:$D$40&"",$B$3:$B$40,$B$3:$B$40&"",$B$3:$B$40,">="&$F$5,$B$3:$B$40,"<="&$G$5)))

There are multiple entries for each plantation each day but I only want to count how many days in a set period the plantation name is mentioned regardless of the number of times it is mentioned each day, so from the image below "LAKE MUIR" is mentioned on 3 days (1st, 7th & 8th). Data will be added to this spreadsheet on a daily basis so ideally, I want the range to have a much bigger range but as soon as I extend the range so it covers other months I get an error. The results below are correct so the formula works but it cannot handle dates in column B not in the range in F5 & G5. I basically want all the references in the above formula to 40 to be 4000.

Thanks

Craig

 

[jasonb75]

Welcome to Mr Excel

See if this works

=SUM(IFERROR(1/COUNTIFS($D$3:$D$40,$D$3:$D$40&"",$D$3:$D$30,$F8,$B$3:$B$40,">="&$F$5,$B$3:$B$40,"<="&$G$5),0))

Rather than increasing the formula to 4000 rows, using a dynamic range would be a better option once the formula is tested and working with a smaller range.

For future reference, it is better to upload data samples in a format that we can copy and paste to excel so that formulas can be tested without having to retype your data first.
If you are able to install the add-in XL2BB - Excel Range to BBCode

 

[Fildes]

Thanks jasonb75.

This hasn't worked, unfortunately, but I have installed the XL2BB as you suggested and now pasted below. When you say to use a dynamic range are you saying assign the column a name. If I change the formula to $D:$D instead $D$2:$D$4000 it takes forever to calculate on this particular formula. I have never seen Excel stall to calculate before even on some long array formulas

Book122.xlsx
	B	C	D	E	F	G	H	I
1
2	Date_1	Date_2	Plantation
3	15/11/19 0:00	15/11/19 8:55	LAKE MUIR
4	15/11/19 0:00	15/11/19 10:27	LAKE MUIR		Start Date	End Date
5	15/11/19 0:00	15/11/19 15:45	LAKE MUIR		01/11/19	30/11/19
6	15/11/19 0:00	15/11/19 16:26	LAKE MUIR
7	15/11/19 0:00	15/11/19 16:35	HICKS		Plantation	Day Count		Mr Excel
8	15/11/19 0:00	15/11/19 18:24	LAKE MUIR		LAKE MUIR	#DIV/0!		1
9	18/11/19 0:00	18/11/19 9:31	LAKE MUIR		PARTRIDGE	0
10	18/11/19 0:00	18/11/19 10:31	LAKE MUIR		HICKS	5
11	18/11/19 0:00	18/11/19 11:57	LAKE MUIR		SHEPHERD	0
12	18/11/19 0:00	18/11/19 15:31	LAKE MUIR		GLENN	0
13	18/11/19 0:00	18/11/19 17:00	LAKE MUIR
14	18/11/19 0:00	18/11/19 18:23	LAKE MUIR
15	18/11/19 0:00	18/11/19 18:52	HICKS
16	19/11/19 0:00	19/11/19 8:59	LAKE MUIR
17	19/11/19 0:00	19/11/19 10:21	LAKE MUIR
18	19/11/19 0:00	19/11/19 11:40	LAKE MUIR
19	19/11/19 0:00	19/11/19 15:02	LAKE MUIR
20	19/11/19 0:00	19/11/19 16:59	LAKE MUIR
21	19/11/19 0:00	19/11/19 17:13	HICKS
22	19/11/19 0:00	19/11/19 18:10	LAKE MUIR
23	20/11/19 0:00	20/11/19 9:50	LAKE MUIR
24	20/11/19 0:00	20/11/19 11:36	LAKE MUIR
25	20/11/19 0:00	20/11/19 11:36	LAKE MUIR
26	20/11/19 0:00	20/11/19 14:32	HICKS
27	20/11/19 0:00	20/11/19 16:02	HICKS
28	20/11/19 0:00	20/11/19 17:49	HICKS
29	21/11/19 0:00	21/11/19 7:38	FOUR WINDS
30	21/11/19 0:00	21/11/19 8:56	FOUR WINDS
31	21/11/19 0:00	21/11/19 10:44	FOUR WINDS
32	21/11/19 0:00	21/11/19 12:18	FOUR WINDS
33	21/11/19 0:00	21/11/19 14:10	FOUR WINDS
34	21/11/19 0:00	21/11/19 15:42	HICKS
35	21/11/19 0:00	21/11/19 15:42	HICKS
36	21/11/19 0:00	21/11/19 15:52	FOUR WINDS
37	21/11/19 0:00	21/11/19 17:18	FOUR WINDS
38	22/11/19 0:00	22/11/19 7:14	FOUR WINDS
39	22/11/19 0:00	22/11/19 9:10	FOUR WINDS
40	22/11/19 0:00	22/11/19 10:06	FOUR WINDS
41	22/11/19 0:00	22/11/19 12:07	FOUR WINDS
42	22/11/19 0:00	22/11/19 12:48	HICKS
43	22/11/19 0:00	22/11/19 13:02	FOUR WINDS
44	22/11/19 0:00	22/11/19 14:53	FOUR WINDS
45	22/11/19 0:00	22/11/19 15:53	HICKS
46	22/11/19 0:00	22/11/19 15:53	HICKS
47	22/11/19 0:00	22/11/19 16:18	FOUR WINDS
48	22/11/19 0:00	22/11/19 17:50	FOUR WINDS
49	25/11/19 0:00	25/11/19 7:08	FOUR WINDS
50	25/11/19 0:00	25/11/19 8:37	FOUR WINDS
51	25/11/19 0:00	25/11/19 9:56	FOUR WINDS
52	25/11/19 0:00	25/11/19 11:19	FOUR WINDS
53	25/11/19 0:00	25/11/19 12:24	FOUR WINDS
54	25/11/19 0:00	25/11/19 13:54	FOUR WINDS
55	25/11/19 0:00	25/11/19 15:06	FOUR WINDS
56	25/11/19 0:00	25/11/19 16:56	FOUR WINDS
57	25/11/19 0:00	25/11/19 18:02	FOUR WINDS
58	26/11/19 0:00	26/11/19 7:19	FOUR WINDS
59	26/11/19 0:00	26/11/19 8:33	FOUR WINDS
60	26/11/19 0:00	26/11/19 10:02	FOUR WINDS
61	26/11/19 0:00	26/11/19 11:48	FOUR WINDS
62	26/11/19 0:00	26/11/19 12:54	FOUR WINDS
63	26/11/19 0:00	26/11/19 14:31	HICKS
64	26/11/19 0:00	26/11/19 16:00	FOUR WINDS
65	26/11/19 0:00	26/11/19 17:02	HICKS
66	27/11/19 0:00	27/11/19 7:13	FOUR WINDS
67	27/11/19 0:00	27/11/19 8:50	FOUR WINDS
68	27/11/19 0:00	27/11/19 10:31	FOUR WINDS
69	27/11/19 0:00	27/11/19 12:01	FOUR WINDS
70	27/11/19 0:00	27/11/19 13:47	FOUR WINDS
71	27/11/19 0:00	27/11/19 14:34	HICKS
72	27/11/19 0:00	27/11/19 16:48	FOUR WINDS
73	27/11/19 0:00	27/11/19 17:17	HICKS
74	28/11/19 0:00	28/11/19 7:52	FOUR WINDS
75	28/11/19 0:00	28/11/19 9:00	FOUR WINDS
76	28/11/19 0:00	28/11/19 10:27	FOUR WINDS
77	28/11/19 0:00	28/11/19 12:42	FOUR WINDS
78	28/11/19 0:00	28/11/19 14:22	FOUR WINDS
79	28/11/19 0:00	28/11/19 15:19	HICKS
80	28/11/19 0:00	28/11/19 17:27	FOUR WINDS
81	29/11/19 0:00	29/11/19 7:05	FOUR WINDS
82	29/11/19 0:00	29/11/19 8:15	FOUR WINDS
83	29/11/19 0:00	29/11/19 9:58	FOUR WINDS
84	29/11/19 0:00	29/11/19 11:39	HICKS
85	29/11/19 0:00	29/11/19 12:44	FOUR WINDS
86	29/11/19 0:00	29/11/19 14:10	HICKS
87	29/11/19 0:00	29/11/19 15:48	FOUR WINDS
88	29/11/19 0:00	29/11/19 15:48	FOUR WINDS
89	29/11/19 0:00	29/11/19 17:04	FOUR WINDS
90	02/12/19 0:00	02/12/19 7:20	FOUR WINDS
91	02/12/19 0:00	02/12/19 8:27	FOUR WINDS
92	02/12/19 0:00	02/12/19 9:51	FOUR WINDS
93	02/12/19 0:00	02/12/19 11:26	FOUR WINDS
94	02/12/19 0:00	02/12/19 12:49	FOUR WINDS
95	02/12/19 0:00	02/12/19 14:37	HICKS
96	02/12/19 0:00	02/12/19 14:52	FOUR WINDS
97	02/12/19 0:00	02/12/19 17:13	FOUR WINDS
98	02/12/19 0:00	02/12/19 17:21	HICKS
99	03/12/19 0:00	03/12/19 7:07	FOUR WINDS
100	03/12/19 0:00	03/12/19 8:13	FOUR WINDS
101	03/12/19 0:00	03/12/19 11:44	FOUR WINDS
102	03/12/19 0:00	03/12/19 12:27	HICKS
103	03/12/19 0:00	03/12/19 13:51	FOUR WINDS
104	03/12/19 0:00	03/12/19 15:27	FOUR WINDS
105	03/12/19 0:00	03/12/19 16:37	FOUR WINDS
106	04/12/19 0:00	04/12/19 7:00	FOUR WINDS
107	04/12/19 0:00	04/12/19 8:26	FOUR WINDS
108	04/12/19 0:00	04/12/19 9:45	FOUR WINDS
109	04/12/19 0:00	04/12/19 11:17	FOUR WINDS
110	04/12/19 0:00	04/12/19 12:18	FOUR WINDS
111	04/12/19 0:00	04/12/19 13:55	FOUR WINDS
112	04/12/19 0:00	04/12/19 15:08	FOUR WINDS
113	04/12/19 0:00	04/12/19 16:30	HICKS
114	04/12/19 0:00	04/12/19 16:46	FOUR WINDS
115	04/12/19 0:00	04/12/19 17:49	FOUR WINDS
116	05/12/19 0:00	05/12/19 7:25	FOUR WINDS
117	05/12/19 0:00	05/12/19 7:58	HICKS
118	05/12/19 0:00	05/12/19 8:58	FOUR WINDS
119	05/12/19 0:00	05/12/19 10:24	FOUR WINDS
120	05/12/19 0:00	05/12/19 11:04	HICKS
121	05/12/19 0:00	05/12/19 12:08	FOUR WINDS
122	05/12/19 0:00	05/12/19 13:26	FOUR WINDS
123	05/12/19 0:00	05/12/19 15:16	FOUR WINDS
124	05/12/19 0:00	05/12/19 17:18	FOUR WINDS
125	05/12/19 0:00	05/12/19 17:18	FOUR WINDS
Sheet1

Cell Formulas
Range	Formula
I8	I8	=SUM(IFERROR(1/COUNTIFS($D$3:$D$40,$D$3:$D$40&"",$D$3:$D$40,$F8,$B$3:$B$40,">="&$F$5,$B$3:$B$40,"<="&$G$5),0))
G8	G8	=IF(COUNTIFS($B$3:$B$4000,">="&$F$5,$B$3:$B$4000,"<="&$G$5)=0,0,SUMPRODUCT((($D$3:$D$4000=F8))/COUNTIFS($D$3:$D$4000,$D$3:$D$4000&"",$B$3:$B$4000,$B$3:$B$4000&"",$B$3:$B$4000,">="&$F$5,$B$3:$B$4000,"<="&$G$5)))
G9:G12	G9	=IF(COUNTIFS($B$3:$B$40,">="&$F$5,$B$3:$B$40,"<="&$G$5)=0,0,SUMPRODUCT((($D$3:$D$40=F9))/COUNTIFS($D$3:$D$40,$D$3:$D$40&"",$B$3:$B$40,$B$3:$B$40&"",$B$3:$B$40,">="&$F$5,$B$3:$B$40,"<="&$G$5)))
B3:B125	B3	=TRUNC(P3)

 

[jasonb75]

Hi, Craig.

Try this corrected version of the formula, because I didn't have data to test the first one on, I didn't notice that one of the ranges was missing (there was a typo, but I see that you corrected that).

=SUM(IFERROR(1/COUNTIFS($D$3:$D$40,$D$3:$D$40,$D$3:$D$40,$F8,$B$3:$B$40,$B$3:$B$40,$B$3:$B$40,">="&$F$5,$B$3:$B$40,"<="&$G$5),0))

Also, I neglected to mention that the formula needs to be array confirmed with Ctrl Shift Enter, not just Enter.

As for dynamic ranges, no not full colums, trying to process over 1 million rows will always cause things to hang. You can do it by defined names, of within the formula. Instead of $D$3:$D$40 you would use $D$3:INDEX($D$D,MATCH("zzzz",$D$D)) using MATCH to find the last row of data.

To make it more efficient, you could enter =MATCH("zzzz",$D$D) into F1, then use $D$3:INDEX($D:$D,$F$1) in the main formula. That way the MATCH function only has to calculate once for all of the formulas rather than 7 times for each formula.

Book1
	B	C	D	E	F	G	H
1					50
2	Date_1	Date_2	Plantation
3	15/11/2019	15/11/2019 08:55:00	LAKE MUIR
4	15/11/2019	15/11/2019 10:27:00	LAKE MUIR		Start Date	End Date
5	15/11/2019	15/11/2019 15:45:00	LAKE MUIR		01/11/2019	30/11/2019
6	15/11/2019	15/11/2019 16:26:00	LAKE MUIR
7	15/11/2019	15/11/2019 16:35:00	HICKS		Plantation	Mr Excel	Dynamic
8	15/11/2019	15/11/2019 18:24:00	LAKE MUIR		LAKE MUIR	4	4
9	18/11/2019	18/11/2019 09:31:00	LAKE MUIR		PARTRIDGE	0	0
10	18/11/2019	18/11/2019 10:31:00	LAKE MUIR		HICKS	5	6
11	18/11/2019	18/11/2019 11:57:00	LAKE MUIR		SHEPHERD	0	0
12	18/11/2019	18/11/2019 15:31:00	LAKE MUIR		GLENN	0	0
13	18/11/2019	18/11/2019 17:00:00	LAKE MUIR		FOUR WINDS	2	3
14	18/11/2019	18/11/2019 18:23:00	LAKE MUIR
15	18/11/2019	18/11/2019 18:52:00	HICKS
16	19/11/2019	19/11/2019 08:59:00	LAKE MUIR
17	19/11/2019	19/11/2019 10:21:00	LAKE MUIR
18	19/11/2019	19/11/2019 11:40:00	LAKE MUIR
19	19/11/2019	19/11/2019 15:02:00	LAKE MUIR
20	19/11/2019	19/11/2019 16:59:00	LAKE MUIR
21	19/11/2019	19/11/2019 17:13:00	HICKS
22	19/11/2019	19/11/2019 18:10:00	LAKE MUIR
23	20/11/2019	20/11/2019 09:50:00	LAKE MUIR
24	20/11/2019	20/11/2019 11:36:00	LAKE MUIR
25	20/11/2019	20/11/2019 11:36:00	LAKE MUIR
26	20/11/2019	20/11/2019 14:32:00	HICKS
27	20/11/2019	20/11/2019 16:02:00	HICKS
28	20/11/2019	20/11/2019 17:49:00	HICKS
29	21/11/2019	21/11/2019 07:38:00	FOUR WINDS
30	21/11/2019	21/11/2019 08:56:00	FOUR WINDS
31	21/11/2019	21/11/2019 10:44:00	FOUR WINDS
32	21/11/2019	21/11/2019 12:18:00	FOUR WINDS
33	21/11/2019	21/11/2019 14:10:00	FOUR WINDS
34	21/11/2019	21/11/2019 15:42:00	HICKS
35	21/11/2019	21/11/2019 15:42:00	HICKS
36	21/11/2019	21/11/2019 15:52:00	FOUR WINDS
37	21/11/2019	21/11/2019 17:18:00	FOUR WINDS
38	22/11/2019	22/11/2019 07:14:00	FOUR WINDS
39	22/11/2019	22/11/2019 09:10:00	FOUR WINDS
40	22/11/2019	22/11/2019 10:06:00	FOUR WINDS
41	22/11/2019	22/11/2019 12:07:00	FOUR WINDS
42	22/11/2019	22/11/2019 12:48:00	HICKS
43	22/11/2019	22/11/2019 13:02:00	FOUR WINDS
44	22/11/2019	22/11/2019 14:53:00	FOUR WINDS
45	22/11/2019	22/11/2019 15:53:00	HICKS
46	22/11/2019	22/11/2019 15:53:00	HICKS
47	22/11/2019	22/11/2019 16:18:00	FOUR WINDS
48	22/11/2019	22/11/2019 17:50:00	FOUR WINDS
49	25/11/2019	25/11/2019 07:08:00	FOUR WINDS
50	25/11/2019	25/11/2019 08:37:00	FOUR WINDS
Sheet3

Cell Formulas
Range	Formula
F1	F1	=MATCH("zzzz",$D:$D)
G8:G13	G8	{=SUM(IFERROR(1/COUNTIFS($D$3:$D$40,$D$3:$D$40,$D$3:$D$40,$F8,$B$3:$B$40,$B$3:$B$40,$B$3:$B$40,">="&$F$5,$B$3:$B$40,"<="&$G$5),0))}
H8:H13	H8	{=SUM(IFERROR(1/COUNTIFS($D$3:INDEX($D:$D,$F$1),$D$3:INDEX($D:$D,$F$1),$D$3:INDEX($D:$D,$F$1),$F8,$B$3:INDEX($B:$B,$F$1),$B$3:INDEX($B:$B,$F$1),$B$3:INDEX($B:$B,$F$1),">="&$F$5,$B$3:INDEX($B:$B,$F$1),"<="&$G$5),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fildes]

Hi Jason, thank you so much for the quick reply; this works nicely. I see I still have a lot to learn.

Well done

Craig

 

[jasonb75]

Glad to hear that it worked, thanks for the feedback

I still have a lot to learn.

Everyone here does, although there are probably a few that think they know everything