# Need Single Formula

#### gnaga

##### Well-known Member
Book1
IJKLMN
60130
71245
82356
97467
1016-Nov-2148778
1117-Nov-215
1218-Nov-216
1319-Nov-217
1420-Nov-210
1521-Nov-211
1622-Nov-212
1723-Nov-213
1824-Nov-214
1925-Nov-215
2026-Nov-216
2127-Nov-217
2228-Nov-210
2329-Nov-211
2430-Nov-212
25
26
27
2816-Nov-218778
2930-Nov-21
Sheet1
Cell Formulas
RangeFormula
K10:N10K10=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:J24J10=MOD(I10,8)
K28:N28K28=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.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try this in K11:

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

Book1
IJKLMN
60130
71245
82356
97467
1011/16/202148778
1111/17/202158778
1211/18/20216
1311/19/20217
1411/20/20210
1511/21/20211
1611/22/20212
1711/23/20213
1811/24/20214
1911/25/20215
2011/26/20216
2111/27/20217
2211/28/20210
2311/29/20211
2411/30/20212
25
2911/30/2021
Sheet1
Cell Formulas
RangeFormula
K10:N10K10=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:N11K11=SUMPRODUCT(COUNTIF(K\$6:K\$9,MOD(\$I\$10:\$I\$24,8)))
J10:J24J10=MOD(I10,8)

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???

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))))

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???

In the formula of #4:

FROM DATE is \$I\$28

TO DATE
is \$I\$29

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???

=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

Book1
IJKLMN
60130
71245
82356
97467
1016-Nov-2148778
1117-Nov-215
1218-Nov-216
1319-Nov-217
1420-Nov-210
1521-Nov-211
1622-Nov-212
1723-Nov-213
1824-Nov-214
1925-Nov-215
2026-Nov-216
2127-Nov-217
2228-Nov-210
2329-Nov-211
2430-Nov-212
25
26
27
2816-Nov-211278
2930-Nov-21
Sheet1
Cell Formulas
RangeFormula
K10:N10K10=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:J24J10=MOD(I10,8)
K28K28=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))))
L28L28=SUMPRODUCT(COUNTIF(L6:L10,MOD(I28:I29,8)))
M28:N28M28=M10

Still the same result of 1 not 8 in Cell K28

Book1
IJKLMN
60130
71245
82356
97467
1011/16/202148778
1111/17/20215
1211/18/20216
1311/19/20217
1411/20/20210
1511/21/20211
1611/22/20212
1711/23/20213
1811/24/20214
1911/25/20215
2011/26/20216
2111/27/20217
2211/28/20210
2311/29/20211
2411/30/20212
25
26
27
2811/16/20218778
2911/30/2021
Sheet1
Cell Formulas
RangeFormula
K10:N10K10=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:J24J10=MOD(I10,8)
K28:N28K28=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

Replies
2
Views
83
Replies
6
Views
434
Replies
2
Views
272
Replies
0
Views
230
Replies
4
Views
282

### Forum statistics

1,203,489
Messages
6,055,722
Members
444,814
Latest member
AutomateDifficulty

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back