Hi Trying to count the number of Workdays only between 2 dates. [Startdate] & [Enddate] can anyone help please. Cheers Sarah

0 |

3/20/2007 7:51:45 PM

This has become quite challenging. IF in range d23:023 there are only zeros, nothing[s], or a mix of both, would like cell q23 to return zero, otherwise 1. Can’t use a sum because sometimes there are offsetting numbers, ie +100 and -100 in the same row that would return 0. Some cells in the range are a formula [which is why there’s a zero], others are just blank. The end result is to import a large table into Access, using row q as import criteria, so Access does not import any rows that are meaningless. In other words, if *something* is in the row, put a 1 in column q, if...

I have 55 clusters, each cluster contains anywhere from 15 to 25 sites (varies), each site may start construction on the same date or may not. I need a formula that counts the sites per cluster that start in a range ie: (< = 4-8-06) , (<=4-15-06) (<=4-22-06) The Data set will contain over 800 sites so I am breaking it up week to week to show how many sites start construction and also need to make it cumulative: Trying to automate this so I can copy and paste the data the "formula" will pull from. From there I have a chart conditionally formatted red yellow green based on ...

Hi, is there a way to display how much objects are in a folder behind the menuitem? An Example: There are 10 Activities associated to an account. Now I want to display these in the left Menu like "Activities (10)" or "Contacts (2)" In Outlook it shows the unread objects in a way I want to. Hope this is possible!! Not without developing your own presentation tier. -- Matt Parks MVP - Microsoft CRM "Torti" <t.malkmus@itnovum.de> wrote in message news:1146748297.804981.235920@i40g2000cwc.googlegroups.com... Hi, is there a way to display how much o...

Hi Im having a few problems with a some excel formula's 1: I need a formula which will calculate a specific word in excel (specific word=Industrial) I have already created a formula =COUNTIF(H10:E12,"*industrial*") It works fine in one specific range (E.g H10:E12). I need to add another range in that formula. Everytime i try, i get an error message. E.G H10: E12 AND F14:G20 2: In the same spreadsheet i have filled in some cells with different colours, using the fill tool Red Blue Green I need a forumla which will calculate each Red cell in a given range? I need a forumla whic...

I want to tally the number of cells in a column that have non zero values. The cout function seem to insist on counting zeros. THe bulk data has plus and minus values. I can separate them but I have to show a true or false or zero rahter than blank (I would prefer blank). So how can I tally the entries in each column with out counting zeros? Assuming the values are all numeric, ie no text values, try =COUNTIF(A1:A100,"<>"&0) Regards, Alan. "Ryan" <Ryan@discussions.microsoft.com> wrote in message news:4FDBB61F-7EAC-421B-A1F2-3AC4617A7F7A@microsoft.co...

Hi, I have a table (tblTrainingSession) and amongst other fields there is a txtVenue field. Then i have a amongst all I forms a frmMainMenu. On this form i have all my buttons etc but i want to do a section on the forms of a glimpse of stats (so various ad-hoc things) One of the things i want to do is to do a count of the txtVenue field (Nulls only) in the tblTrainingSession. How do i do this? I thought i could add a txtbox to the frmMainMenu and then right-click and go to Expression Builder and build the following: "= [tblTrainingSession]![txtVenue] = Null&qu...

Hello, I've got Outlook 2002 SP3 and have the Shortcut bar visible on the left side of the screen. The inbox says there are 6 new messages when in fact there are only 2 new messages. If I go in and mark those 2 as read, the Shortcut bar says there are 4 when in fact there are none. Is there a way to fix this so it displays the correct number? It was working fine up until yesterday and I've had this computer with this version installed for over a year. Thanks for the help. Mark Try scrolling all through your Inbox - happened to us when 'someone' sent us emails from a ...

This is a repost of my original question: I am trying to not use any add-ins or BI tools. I'm wanting to use a pivot table to create a histogram (graph) that shows: A.) Count of new action items for the week B.) Count of action items resolved for the week C.) The cumulative count of unresolved action items for the week My columns are in a table called tblMain and I figured I could get by using columns [ID] (a unique ID number) and [DateComplete] (a short date). Filtering out the action items that weren't completed is easy enough using a "null" qualifier for the da...

Good day! When pasting a large amount of data into an Access table, I get the error message "File sharing lock count exceeded. Increase Max Locks Per File registry entry". I am aware of http://support.microsoft.com/?id=815281. Under Method 2 it states in that article to load "Microsoft DAO 3.6 Object Library". When I attempt that, I get error message "Name conflicts with existing module, project, or object library". I use Access 2002 (SP3). Under VB Editor, Tools, References I find: Microsoft Access 10.0 Object Library Microsoft DAO 2.5/3.51 Compatibility Lib...

I would like to know if there is a way from exchange server 2003 or outlook 2003. to run a report or something to that affect to tell me how many emails I received/sent last month? I think you will need to look at 3rd party reporting tools. http://www.msexchange.org/software/Reporting/ Nue "Tim65" <Tim65@discussions.microsoft.com> wrote in message news:D80FF886-299B-48E2-AD21-6E71AD47C688@microsoft.com... >I would like to know if there is a way from exchange server 2003 or outlook > 2003. to run a report or something to that affect to tell me how many > emails &...

=COUNTIF(A1:A100,"<" & NOW()) This works good for specific cells. I have a similar question. How would I do the same thing given that: 1) You only have a starting date 2) Your only wanting to count work days (in this case 4 day/wk) up to NOW() Thank you for all your help. Aviator On Thu, 6 Jan 2005 12:53:03 -0800, "Aviator" <Aviator@discussions.microsoft.com> wrote: >=COUNTIF(A1:A100,"<" & NOW()) > >This works good for specific cells. I have a similar question. > >How would I do the same thing given that: >1) You only ha...

Hello, I have a large set of numerical data (cells I8:O24607) in which each cell has been formatted to have either no fill or one of 5 different colored (light yellow, yellow, orange, red, or black) fills. The cell values and formatting were copied and pasted into this worksheet from other worksheets so there is no conditional formatting applied to the current sheet. The formatting represents a quality rating and I would like to count the number of cells that have either no fill or the light yellow fill in each column. Is this possible to do using the IF function and having the cell fill be ...

On a worksheet i have column that contains the length of service (in monnths ) of each of my employees. I want to a formulae that will segregate them into sertain groups e.g 0-7, and count how many of each group i have. I have tired using the countif and sum functions, but to no avail. could someone please give me some direction? lots of thanks colin Take a look at =frequency() in excel's help. I think that'll do what you want. Colin Matchett wrote: > > On a worksheet i have column that contains the length of > service (in monnths ) of each of my employees. I wa...

I have a workbook of several sheets which have hundreds of rows of data, and I want to make a summary sheet which takes data from one column of each sheet. In the summary sheet I want to see how many items are in the sheet within a given range. For instance the table is titled gains The ranges are 1-500, 500-1000, 2000-5000, 5000-10000, and 10000+ I'm pulling the data from column N of sheets titled NE NW SE MW SW and GL. What counting formula could I use? I think I'd add a new worksheet and then copy each of those column N's into one giant column in that new worksheet. ...

Hi all, Newbie here I want to add subtotals to my spreadsheet. For example, I have a companyID, Employee ID, VacationDaysLeft, VacationDaysUsed. I'm trying to insert subtotals (grouping by company ID). I want to know how many employees used more than 10 days of vacation, and how many emplyees have more that 12 days of vacation left (this is an example only) If I try to use the subtotals feature in excel, it will break it down by companies, and it will generate subtotals for each company using SUBTOTAL(3,..) - where 3 is the id for COUNTA. CountA is not good enough. What I would need that t...

hi i have the following formula that counts a range depending on the date SUMPRODUCT(--(TEXT($A$1:$A$350,"dd-mm")=TEXT(AA1,"dd-mm")),$E$1:$E$350) AA1 = 01-03-2004 dates 01-03 this returns a value for all the date range i need to refine this further to count between to date values ie one weeks values ie 01-03-04 to 07-03-04 08-03-04 to 15-03-04 thanks in advance kevin Hi try =SUMPRODUCT(($A$1:$A$350>=DATE(2004,3,1))*($A$1:$A$350<=DATE(2004,3,7)) ,$E$1:$E$350) -- Regards Frank Kabel Frankfurt, Germany kevin carter wrot...

Hi there, i have 2 columns of data, 8 to 9 thousand entries per column, i woul like to filter the columns and then count the filtered cells. example: Column one is Speed (0-35 cm/sec) Column two is Direction (0-360 degrees) i would like to sort column one for all entries between 0 & 3 cm/sec and then sort column 2 for entries between 0 & 15 degrees, then i woul like to count the results and paste the number to a seperate cell i another work sheet. Note column 1 & 2 are organized by a time stamp in a seperate column. thank -- Message posted from http://www.ExcelForum.com I...

HI All, In my table there are 3 columns. Each cell in the last column holds either 1 or 0. I want to get the sum of the cells of the last column on top of the column name. I used the sum function to do this and works fine. But my requirement is to get the sum of the cells in the last column when i Filter using 1st and 2nd columns. e.g. Filter using the first column would give 10 rows from 30 rows. I need the sum of the cells of the third row only for those 10 rows. How can I achieve this using functions? Thank You in advance. Gihan. Use SUBTOTAL(9, range) -- ____________________________...

Hi, Would anyone know a good way to count the number of times a numbe appears in a range. I have a bunch of house prices, and want to coun the number that are less than 70000, 70000 to 79000, 80000 to 90000 etc. Any help would be greatly appreciated. Thanks. Dusti -- Message posted from http://www.ExcelForum.com One way: Assume your prices are in A1:Ax. then put this in B1: B1: 70000 B2: 80000 B3: 90000 B4: 100000 .... B14: 200000 Then select C1:C15 and array-enter (CTRL-SHIFT-ENTER or CMD-RETURN): =FREQUENCY(A1:Ax,B1:B14) In article <dustortion.1agbb5@excelforum-nospam.com&...

Hello, I have a requirement to assign a case to a manager whenever all the dependent activities are closed. I tried creating a workflow with a wait condition on "Workflow-Activity Count Including Workflow == 0" with the following assumptions: - the value "Activity Count Including Workflow" will contain the number of *open* activities, and it will decrease whenever activities are closed. - The wait condition will exit when this number reaches zero. I have had no success with any of these assumptions. The workflow gets stuck in the wait condition, and it never exits, ...

Dear Expert, Would like to count the frequency each time it shows up . Below is the example .... 4 shows up 3 times ... 5 shows up 1 time ......... etc etc 7 shows up 3 times, 1 shows up 2 times at the bottom.... Is there any function that can capture this coutning on each movement? Numbers Times 4 4 4 3 5 1 4 4 2 5 5 2 4 4 4 3 3 1 6 6 6 3 7 7 7 3 1 1 2 Hi With numbers in A2 and down, insert this formula in B2 and copy it down as required: =IF(A2<>A3,COUNTIF($A$2:A2,A2)-SUMPRODUCT(--($A1:A$2=A2),$B1:B$2),"") Regards, Per...

Is there a way to count back a certain number of active cells only (eg. 40 possible cells, only 28 are active), and then enter the lowest number within that range into a cell? What defines active?: -- HTH RP (remove nothere from the email address if mailing direct) "Sac73" <Sac73@discussions.microsoft.com> wrote in message news:207089F2-80B4-46C8-863D-A639CAD16155@microsoft.com... > Is there a way to count back a certain number of active cells only (eg. 40 > possible cells, only 28 are active), and then enter the lowest number within > that range into a cell? ...

Hi, I need to calculate the average of a column in a pivot for eg. Jan 09 and Feb 09 so on and so forth for the whole year. Now how can i calculate the average of count of Jan 09 and Feb 09 ie. (Jan 09 + Feb 09)/2 . Data in the column just says Jan 09, feb 09 , mar 09 so I have to take their count against other columns and then take its average. I tried calculating item in the pivot but its giving error "Calculated items do not work with customs subtotal". If you can advise the above or its alternative, I'll be greatful. Regards, KD ...

here is the data a 1 b 2 c 1 a 3 a 2 c 3 a 1 like to count no of rows which has a and 1, in this case will be 2. any advice? =SUMPRODUCT((FirstRange="a")*(SecondRange=1)) (replace FirstRange and SecondRange with references to your data) Arvi Laanemets "Soe" <soenandar@hotmail.com> wrote in message news:O88yaSedDHA.1128@tk2msftngp13.phx.gbl... > here is the data > > a 1 > b 2 > c 1 > a 3 > a 2 > c 3 > a 1 > > like to count no of rows which has a and 1, in this case will be 2. > any ...

I'm trying to count the number of text-strings, in this case commas, within a cell. For instance: A1(text)=AB,BC,CD,DE,EF If I could count 4 commas, I'd know there were (4)+1 objects in the cell... Thanks in advance. I always research as much as possible before asking the forum. The forum has ALWAYS come through with the answer! --- Message posted from http://www.ExcelForum.com/ Something like: =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) or more generic: =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"SOMETEXTHERE",""))/LEN("SOMETEXTHERE") &q...