# Find offday using formula

#### Thiyagu

##### Board Regular
Hi there,
Anyone pls help me providing formula to find no of "offday" at cell C2.
The reference is at rightside date & Calender .All this data start at cell A1 to E10.

Start Date End Date No of "offday" Date Calender
05-Aug 13-Aug 2 5-Aug-10 Working
6-Aug-10 Working
7-Aug-10 Working
8-Aug-10 offday
9-Aug-10 Working
10-Aug-10 Working
11-Aug-10 offday
12-Aug-10 Working
13-Aug-10 Working

Many thanks.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Thiyagu

##### Board Regular
Hi there,
Anyone pls help me providing formula to find no of "offday" at cell C2.
The reference is at rightside date & Calender .All this data start at cell A1 to E10.

Start date End date No of offdays Date Calender
5 Aug 13 Aug 2 5 aug working
6 Aug working
7 aug working
8 aug offday
9 aug working
10 aug working
11 aug offday
12 aug working
13 aug working
14 aug working

Resent due to earlier example not so clear

#### Mr_Roscoe

##### Board Regular
Thiyagu,

I suspect what you require is a COUNTIF formula in cell C2 as follows:

C2=COUNTIF(E2:E11,"offday")

E2:E11 is the list with working or offday - adjust this range if required.

cheers, Ian R.

#### Thiyagu

##### Board Regular
Resent again cause still not so clear

Hi there,
Anyone pls help me providing formula to find no of "offday" at cell C2.
The reference is at rightside date & Calender .All this data start at cell A1 to E10.

Start date----------End date------ No of offdays-----
5 Aug--------------13 Aug ----------2------------

Date-----Calender
5 aug----- working
6 Aug -----working
7 aug------working
8 aug ------offday
9 aug-------working
10 aug------working
11 aug------offday
12 aug------working
13 aug------working
14 aug------working

#### Thiyagu

##### Board Regular

Hi there ,

thanks a lot for ur help ...the formula at C2 should count no of offdays based on "start days" to "End days"..

#### AlphaFrog

##### MrExcel MVP
Excel Workbook
ABC
1Start DateEnd DateNo of "offday"
25-Aug13-Aug2
36-Aug-10Working
47-Aug-10Working
58-Aug-10offday
69-Aug-10Working
710-Aug-10Working
811-Aug-10offday
912-Aug-10Working
1013-Aug-10Working
...
Cell Formulas
RangeFormula
C2=SUMPRODUCT(--(A3:A20>=A2),--(A3:A20<=B2),--(B3:B20="offday"))

Last edited:

#### Thiyagu

##### Board Regular

I thinks this solution should work ,,i not try yet..
really thanks a lot for this solution,,

again thanks a lot...

#### Thiyagu

##### Board Regular
i had try ..it's work ...Thanks a lot for AlphaFrog & Mr_Roscoe..

Thanks a lot....many thanks

#### AlphaFrog

##### MrExcel MVP
If you have Excel 2007 or later, you could use the COUNTIFS function instead of the SUMPRODUCT function

=COUNTIFS(A3:A20, ">="&A2, A3:A20, "<="&B2, B3:B20, "offday")

#### Thiyagu

##### Board Regular
I use excel 2003 , it's work great ..again thanks a lot for ur help

Replies
2
Views
217
Replies
1
Views
129
Replies
6
Views
259
Replies
10
Views
222
Replies
11
Views
513

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,487
Messages
5,831,988
Members
430,100
Latest member
namhnz

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