Need Single Formula

[gnaga]

Book1
	I	J	K	L	M	N
6			0	1	3	0
7			1	2	4	5
8			2	3	5	6
9			7	4	6	7
10	16-Nov-21	4	8	7	7	8
11	17-Nov-21	5
12	18-Nov-21	6
13	19-Nov-21	7
14	20-Nov-21	0
15	21-Nov-21	1
16	22-Nov-21	2
17	23-Nov-21	3
18	24-Nov-21	4
19	25-Nov-21	5
20	26-Nov-21	6
21	27-Nov-21	7
22	28-Nov-21	0
23	29-Nov-21	1
24	30-Nov-21	2
25
26
27
28	16-Nov-21		8	7	7	8
29	30-Nov-21
Sheet1

Cell Formulas
Range	Formula
K10:N10	K10	=COUNTIF($J$10:$J$24,K6)+COUNTIF($J$10:$J$24,K7)+COUNTIF($J$10:$J$24,K8)+COUNTIF($J$10:$J$24,K9)
J10:J24	J10	=MOD(I10,8)
K28:N28	K28	=K10

I need single formula in the Yellow Cell.

Basically I want to get number of days falling on specific category between two dates.

Input : From Date & To Date
Category : Mod(Date,8) = 0,1,2,3,4,5,6,7

Output : Number of days for each category.

 

[Phuoc]

Try this in K11:

=SUMPRODUCT(COUNTIF(K$6:K$9,MOD($I$10:$I$24,8)))

Book1
	I	J	K	L	M	N
6			0	1	3	0
7			1	2	4	5
8			2	3	5	6
9			7	4	6	7
10	11/16/2021	4	8	7	7	8
11	11/17/2021	5	8	7	7	8
12	11/18/2021	6
13	11/19/2021	7
14	11/20/2021	0
15	11/21/2021	1
16	11/22/2021	2
17	11/23/2021	3
18	11/24/2021	4
19	11/25/2021	5
20	11/26/2021	6
21	11/27/2021	7
22	11/28/2021	0
23	11/29/2021	1
24	11/30/2021	2
25
29	11/30/2021
Sheet1

Cell Formulas
Range	Formula
K10:N10	K10	=COUNTIF($J$10:$J$24,K6)+COUNTIF($J$10:$J$24,K7)+COUNTIF($J$10:$J$24,K8)+COUNTIF($J$10:$J$24,K9)
K11:N11	K11	=SUMPRODUCT(COUNTIF(K$6:K$9,MOD($I$10:$I$24,8)))
J10:J24	J10	=MOD(I10,8)

 

[gnaga]

Thanks Phuoc ? for your response. Your solution works in a way. Is there a possibility to give only from date & to date and get the result?? May be a kind of array formula....Any thoughts???

 

[Phuoc]

Change to:

=SUMPRODUCT(COUNTIF(K$6:K$9,MOD($I$10:$I$24,8)+8*(($I$10:$I$24<$I$28)+($I$10:$I$24>$I$29))))

 

[gnaga]

Once again Thanks Phuoc ?. In my example just for the clarity, I expanded my date range in the rows from I10:I24. I do not want to do this. Date Input to the formula from two cells only. One for FROM DATE and the second for TO DATE.

Is this possible???

 

[Phuoc]

In the formula of #4:

FROM DATE is $I$28

TO DATE is $I$29

 

[gnaga]

When I Use the formula like the one bellow, it gives output of 1 which suppose to be 8.

=SUMPRODUCT(COUNTIF(K$6:K$9,MOD($I$28:$I$29,8)+8*(($I$28:$I$29<$I$28)+($I$28:$I$29>$I$29))))

Am I doing any mistake???

 

[Phuoc]

=SUMPRODUCT(COUNTIF(K$6:K$9,MOD($I$28:$I$29,8)+10^35*(($I$28:$I$29<$I$28)+($I$28:$I$29>$I$29))))

Try change 8 to 10^35

 

[gnaga]

Book1
	I	J	K	L	M	N
6			0	1	3	0
7			1	2	4	5
8			2	3	5	6
9			7	4	6	7
10	16-Nov-21	4	8	7	7	8
11	17-Nov-21	5
12	18-Nov-21	6
13	19-Nov-21	7
14	20-Nov-21	0
15	21-Nov-21	1
16	22-Nov-21	2
17	23-Nov-21	3
18	24-Nov-21	4
19	25-Nov-21	5
20	26-Nov-21	6
21	27-Nov-21	7
22	28-Nov-21	0
23	29-Nov-21	1
24	30-Nov-21	2
25
26
27
28	16-Nov-21		1	2	7	8
29	30-Nov-21
Sheet1

Cell Formulas
Range	Formula
K10:N10	K10	=COUNTIF($J$10:$J$24,K6)+COUNTIF($J$10:$J$24,K7)+COUNTIF($J$10:$J$24,K8)+COUNTIF($J$10:$J$24,K9)
J10:J24	J10	=MOD(I10,8)
K28	K28	=SUMPRODUCT(COUNTIF(K$6:K$9,MOD($I$28:$I$29,8)+10^35*(($I$28:$I$29<$I$28)+($I$28:$I$29>$I$29))))
L28	L28	=SUMPRODUCT(COUNTIF(L6:L10,MOD(I28:I29,8)))
M28:N28	M28	=M10

Still the same result of 1 not 8 in Cell K28

 

[Phuoc]

Book1
	I	J	K	L	M	N
6			0	1	3	0
7			1	2	4	5
8			2	3	5	6
9			7	4	6	7
10	11/16/2021	4	8	7	7	8
11	11/17/2021	5
12	11/18/2021	6
13	11/19/2021	7
14	11/20/2021	0
15	11/21/2021	1
16	11/22/2021	2
17	11/23/2021	3
18	11/24/2021	4
19	11/25/2021	5
20	11/26/2021	6
21	11/27/2021	7
22	11/28/2021	0
23	11/29/2021	1
24	11/30/2021	2
25
26
27
28	11/16/2021		8	7	7	8
29	11/30/2021
Sheet1

Cell Formulas
Range	Formula
K10:N10	K10	=COUNTIF($J$10:$J$24,K6)+COUNTIF($J$10:$J$24,K7)+COUNTIF($J$10:$J$24,K8)+COUNTIF($J$10:$J$24,K9)
J10:J24	J10	=MOD(I10,8)
K28:N28	K28	=SUMPRODUCT(COUNTIF(K$6:K$9,MOD($I$10:$I$24,8)+10^35*(($I$10:$I$24<$I$28)+($I$10:$I$24>$I$29))))

Change $I$28:$I$29 to $I$10:$I$24