# 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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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

ADVERTISEMENT

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

ADVERTISEMENT

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
3
Views
59
Replies
5
Views
57
Replies
11
Views
114
Replies
3
Views
48
Replies
3
Views
32

Threads
1,108,931
Messages
5,525,684
Members
409,660
Latest member
1817538628