Find offday using formula

[Thiyagu]

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.

 

[Thiyagu]

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]

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]

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]

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]

Excel Workbook
	A	B	C
1	Start Date	End Date	No of "offday"
2	5-Aug	13-Aug	2
3	6-Aug-10	Working
4	7-Aug-10	Working
5	8-Aug-10	offday
6	9-Aug-10	Working
7	10-Aug-10	Working
8	11-Aug-10	offday
9	12-Aug-10	Working
10	13-Aug-10	Working
...

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(A3:A20>=A2),--(A3:A20<=B2),--(B3:B20="offday"))

 

[Thiyagu]

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

again thanks a lot...

 

[Thiyagu]

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

Thanks a lot....many thanks

 

[AlphaFrog]

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]

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