Count Formula

D

Denise B

I have a spreadsheet with three tabs. They all have
people requesting time off...first sheet is for people
requesting more than a week off - 2nd for 1 week at a
time - 3rd for people just wanting 1 day off. The sheets
have their name then 2 columns one for days off starting
and the other for days off finishing (With the exception
of people just wanting one day off)

I would like to have another sheet counting the number of
people off on a daily basis. It is easy enough
using "counif" for the people asking for 1 day off...but I
can't seem to easily break out the weekly and plus
requests.

Any ideas?

Thx,

Denise
 
B

Bob Phillips

Denise,

=COUNTIF(Sheet1!B1:B100,--("2004-06-16"))+SUMPRODUCT((Sheet2!B1:B100<=(--("2
004-06-16")))*(Sheet2!C1:C100>=(--("2004-06-16"))))+SUMPRODUCT((Sheet3!B1:B1
00<=(--("2004-06-16")))*(Sheet3!C1:C100>=(--("2004-06-16"))))

Better to put the date to test in a cell, say H1, and use

=COUNTIF(Sheet1!B1:B100,H1)+SUMPRODUCT((Sheet2!B1:B100<=H1)*(Sheet2!C1:C100>
=H1))+SUMPRODUCT((Sheet3!B1:B100<=H1)*(Sheet3!C1:C100>=H1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top