Count Formula #4

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

 
0
anonymous (74717)
6/21/2004 8:37:56 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
674 Views

Similar Articles

[PageSpeed] 16

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)

"Denise B" <anonymous@discussions.microsoft.com> wrote in message
news:1fabc01c457cf$a2199990$a001280a@phx.gbl...
> 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
>
>


0
bob.phillips1 (6510)
6/21/2004 9:09:25 PM
Wow! Thank you very much!  This will be of much use to me.
>-----Original Message-----
>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)
>
>"Denise B" <anonymous@discussions.microsoft.com> wrote in 
message
>news:1fabc01c457cf$a2199990$a001280a@phx.gbl...
>> 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
>>
>>
>
>
>.
>
0
anonymous (74717)
6/22/2004 1:24:11 PM
Reply:

Similar Artilces:

Formula Excel
Wondering if someone would be able to help me with this: A B C Year Year Service 01-Sep-89 30-Jun-10 = 21 Years Teachers From September 1, 1989 to June 30, 1990 = 1 Year of Service. I know it only 10 months, but that is their year. Is there a formula that could caculate the number of years from September 1, 1989 to June 30, 2010. -- Newfie The general formula for years between dates is =Year(latest date) - Year(earlier date) and 2010-1989 = 21 years. So that might work for most cases? A complication arises if they begin, but do...

grade sheet formula
im a teenager trying to do a project for school on excel. i need to re-do my grade history sheet on excel. this is fine, i've done it all, and i'm fine on that part. where i'm lost at is the points part. the thing is for every class i have a grade. for every grade there are points (A=4, B=3, C=2, D=1, F=0). that and also, if one of my classes is honors gifted, a bit higher in level, you get an extra point (A=5, B=4, C=3, D=2, F=1). and if you're in an AP class, advanced placement, it's 2 more points (A=6, etc.). what i need to know is a formula so that whatever grade...

Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can have the pivot table return the number of rides, but what I need is the number vans that participated for a certain day. Example, it is possible to have 27 trips done by 18 vans, how can I count the number of vans and not addup the van number? Any help appreciated. Thank you, Joe Hi Joe We need a bit more information on how your source data table is set up. What information do you have in each column? -- Regards Roger Govier sacredarms <sacredarms@discussions.microsoft.com> wrote: > I have a pi...

Help with formula for IF ANY
Is there an ANY formula for an excel Cell?? i want to set the conditional formatting for a range of cells in a excel 2007 column to the text "OFF" currently i have the formatting for each cell in the range to highlight if the value contains "OFF" i would like to have the entire range of cells highlighted if ANY of the cells in the range contain "OFF" Am i nuts or is this possible?? Thanks for your help Select your range (say C7:e9) Then use formula is: =countif($c$7:$e$9,"off")>0 And apply the format you like. Barry A&P wrote: > >...

OutLook Today Customization #4
There are 2 types of unread message. I know some messages have come an I know the severity of the message so I leave those to be read som time later. But some messages have come newly I have not seen at all Is it possible to have these category in the outlook today bar? So tha we can directly go to the folder where the new mails have come instea of going thro each folder? Is there any other way to achieve the same? Ramesh ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

Report Writer Question #4
I had modifed the existing Transaction Inquiry Report by Vendor to include Totals. The out of the box report does not have grand total if you print it. The AP persons uses this report as opposed to the AP aging due to their preference of format and ease of running the report. The issue has come about where one of the transaction listed is a Void transaction and although the inquiry window is correctly listing it - but since I modified the report to give me a grand total of all transactions - by including the void in the grand total we are doubling this transaction. Therefore, I need ...

Cycle Counting
Hello, I am looking for ideas on how to cycle count serial number tracked items without increasing workload. Our serialized items are barcoded but the problem is the are situated on the pallet incorrectly and the pallet is placed on a third level of a racking system. If we could just cycle count based on item count and not include the serial number it would make it easy. Is this possible in Great Plains. We are running Great Plains 8.0. Any suggestions are greatly aprreciated. Thank You, Brian Morris Database Administrator ...

Formula in MSP
Hi, I am using MSP 2003. I want to know about all the customizing formulas used in MS project with examples. It is there any website, how the formula we can use in ms project?. Thanks In the Project Help screen, type in "formula" and you should get an option for all formulas in custom fields. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > Hi, > I am using MSP 2003. I want to know about all the customizing > formulas > used in MS project with examples. It is there any website, how the > formula we > can use in ms project?. ...

Pasting a Formula into a new column or worksheet, but so that it doesn't change
Hello - When I copy and paste a formula into a new column, Excel assumes that I wanted to use references to different columns. That is, each time that I want to copy and paste a formula into a new column, or sheet, I have to either first make it an absolute reference---or I have to paste it into Word, and then paste it into Excel to keep it as the same formula. Does anyone know if there is an easier way to paste a formula so that the reference doesn't change, and the formula thus stays the same? Thanks!! One way is to copy it *from the formula bar*! Click in the cell containing the fo...

Formulas don't work in certain cells #3
One formula is: =SUM(C10:C15) another: =SUM(O16/6363) another: =SUM(C19:C36) these same formulas work in the cells right next to these wit different cell references. However, no matter which cell I put th above formulas in, the result is the same - either blank with a dash i the cell or zero. I checked all the cells that the formulas refer t and none has any errors. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View...

Count
Hi, I would like to know how which formula I should use to count some codes & to ignore the duplicated codes, for example in column A: Code 1234 4321 4321 1234 1234 3214 Now, I want Excel to count & the result is 3 which are: 1234-4321-3214 Yes I can use PivotTable, but I would like to use a formula because I have to deal with this every day. I appreciate your support. -- MFS22 COUNTIF will do it. =COUNTIF(Rng,Rng), where the criterion is the same as the range: e.g =COUNTIF(A3:A8,A3:A8) HTH Peter "MFS" wrote: > Hi, > I wo...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

format cells #4
hi. how can i set by default the cell formatting to 'general' or 'number' or even 'text'? i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to 09.dec. is this possible? thanks. (i have some programs which export data to excel and instead of numbers in excel appear dates and i can't set the cell format before the data is exported) -- notrace2004 ------------------------------------------------------------------------ notrace2004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34817 View this thread: http://www....

How do I open Excel 4.0 files in Office 2000?
I have Excel files with Macros, originally saved in Ver 4.0 (Office 97). Now running Office 2000 professional and when I try to open these files I just get a security warning and the files won't open. Hi have you tried setting the security level to 'Medium' ('Tools - Macros - Protection') "Tom_Hunn" wrote: > I have Excel files with Macros, originally saved in Ver 4.0 (Office 97). Now > running Office 2000 professional and when I try to open these files I just > get a security warning and the files won't open. ...

count number of characters in rich edit control
How can i count number of characters in rich edit control 2 If u mean controls of type Rich TextBox control, try RichControl1 RC1; CString Mes=RC.GetText(); int n=Mes.GetLength(); "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i count number of characters in rich edit control 2 EM_GETTEXTLENGTHEX, WM_GETTEXTLENGTH, WM_GETTEXT, EM_STREAM* "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i co...

not calculating formulas
in some excel sheets, after a while it stops calculating formulas. if i copy the hole sheet and paste it into another, everything works fine. Any chance that on the: tools|options|Calculation tab| calculation was set to manual? doros wrote: > > in some excel sheets, after a while it stops calculating formulas. if i copy > the hole sheet and paste it into another, everything works fine. -- Dave Peterson No. I have allready checked that. "Dave Peterson" wrote: > Any chance that on the: > tools|options|Calculation tab| > calculation was set to manual? > &...

Microsoft Office #4
Re Office 2004 fpr Mac, Version 11.0 (040408) Whenever I open Word or Excel document I get the error message "An unexpected error occurred while trying to load the Microsoft Framework library". I suspect that this nhas something to do with the inordinate time it takes for the program to auto-save, sometimes of the order of 20-30 minutes. When this happened recently I tried to Force Quit Word where the screen showed that Word was "not responding". To say that I am disappointed with the product is an understatement, but perhaps there is a very simple explanation. Can any...

Line count
I am working with a program called Dictaphone. We use Word 2007 in this program. The line count on Dictaphone and the line count in Word 2007 are different even though the Dictaphone text is retrieved from Word 2007. Why would they be different? What is the difference between virutal line count and physical line count? ...

Is there any way to use longer strings in formulas?
In Excel 2002, the longest string I can use in a formula is (to my mind) ludicrously short. I have a workbook that requires HUNDREDS of English sentences like: ="Just to let y'all know, thanks to all the swell work by our sales staff in 2007, and despite our having to fire half of them for insubordination, and despite our chairman's having run off to a region in Asia which we believe is officially called the Glorious Former Soviet Autonomous People's Provisional Revolutionary Socialist Republic of Trans-Caucasian Equitorial Guinea, our company's annual return on investme...

locking formulas?
Is there a way to lock formulas??? For example, I am producing this spreadsheet to distribute to several departments -- each department will enter their own information, but if they enter wrong info and delete it, the formulas are deleted as well...how do i lock formulas, but allow users to enter info?? Hi first select the cells for which you want to allow entries. Then goto 'Format - Cells - Protection' and uncheck 'Locked'. Afterwards protect the sheet -- Regards Frank Kabel Frankfurt, Germany internet team wrote: > Is there a way to lock formulas??? For example,...

Creating a conditional formula to increment #s in a test script
Greetings, I have an Excel question. I am creating a test script template for our team and I an in need of some formula help. Here is what I wish to do: Let's say I have a column A in my test script. I wish to label this column with my test script # TC1 TC2 TC3...(and so on) However, the problem is I have section breaks in my test script that divides the document into sections: eg.. <LOGIN SECTION> TC1 TC2 TC3 <LOGOUT SECTION> TC4 TC5 TC6 Now the problem is that if I want to add a new test case to the "Login section" above, I need ...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! Look for =workday() in Excel's help. (Also part o...

Formula wanted
Hi. I have two columns in Excel. In first column A1:A100 i have some integers and empty cells and in second B1:B100 I have some stings and also some empty cells. I would like to apply a formula in C1:C100 somehow in a way: a) to include all stings Bi that the corresponding Ai are numbers b) with non blank cells in C1:C100 (That means all the strings in C column will be in successive order. For example C1=ABC, C2=GEORGE, C3=CCD e.g.) Any idea? Thank you. Try this array formula =IF(ISERROR(SMALL(IF($A$1:$A$20<>"",ROW($A$1:$A$20)),ROW(A1))),"", ...

Counting sales
I have two different datasets. Each with a common identifier. I need a formula that will reference the identifiers in both datasets and count the number of sales a particular agent made. Example. look up this identifier in the other dataset and then give me the value that is 2 columns over from the identifier in the second dataset. See if this helps: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "Dave" <Dave@discussions.microsoft.com> wrote in message news:E7245B37-033F-400E-9CEE-9822BE7CB0F5@microsoft.com... >I have two...

Counting Cells with Conditional Formatting
Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. Here's a UDF (Put in a Standard module) and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRa...