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.

 

[gnaga]

Please refer my Post # 5. My Dates are not expanded each day. It is only stored in two cell. FROM DATE in I28 and TO DATE in I29. Just for the explanation purpose I expanded that in the range I10:I24.

 

[bebo021999]

This use start date in I28, end date in I29

K28:

=SUMPRODUCT(COUNTIF(K$6:K$9,MOD(ROW(INDIRECT($I$28&":"&$I$29)),8)))

Drag accross

 

[gnaga]

This use start date in I28, end date in I29

K28:

=SUMPRODUCT(COUNTIF(K$6:K$9,MOD(ROW(INDIRECT($I$28&":"&$I$29)),8)))

Drag accross

Perfect bebo021999 ???. This is what I was expecting. Thank you so much..???

 

[gnaga]

=SUMPRODUCT(COUNTIF(K$6:K$9,MOD(ROW(INDIRECT($I$28&":"&$I$29)),8)))

I would like to modify the above formula to get the desired result for the specific weekday for example Sunday. I modified like the below one but getting zero as result.

=SUMPRODUCT(COUNTIF(K$6:K$9,MOD(ROW(INDIRECT($I$28&":"&$I$29)),8)),WEEKDAY(ROW(INDIRECT($I$28&":"&$I$29)))=1)

Where is the mistake??? Any idea folks!!!

 

[bebo021999]

Like this?

=SUMPRODUCT(COUNTIF(K$6:K$10,MOD((ROW(INDIRECT($I$28&":"&$I$29))/(WEEKDAY(ROW(INDIRECT($I$28&":"&$I$29)))=1)),8)))

 

[gnaga]

Excellent bebo021999 ??. Thank you very much ???. It works nice..