Count Non-zero Items Occurring Between 2 Known Dates
Feb 12, 2014
For each of my staff I need to count if they worked 15 or more days in the 30 days preceding a statutory holiday. (As a follow on problem... If they have worked 15+ shifts then I'll need to calculate the sum of their hours over the 30 days / 30).
I tried the functions below but they didn't work. (the Name1 column records the number of hours worked that day's shift) The actual count in my test case is 5.
I'm trying to keep track of training dates for employees.
I have my spreadsheet conditionally formatted to highlight dates according to months, based on if the training date is due: next month, this month, last month, or 2 months ago; all different colors.
I used "Format only cells that contain" and then used "Dates occurring." The spreadsheet worked perfectly until this month; evidently it doesn't recognize Jan of 2010 being "Next Month," so those cells aren't highlighted. Current formatting is custom, mmm-yy. I tried different date formats but it didn't change anything.
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column.
What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc
I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks.
im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.
Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date
But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero
Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!
column A is dates WITH time, appears like so: 2013-07-13 20:25:02 column B is the product name column C is the quantity
now I want to search certain items sold between two dates AND times which will then give me the total amount of that item sold between those dates and times
now I know how to do this with date but have a problem doing it with dates and times
I need a formula which can count the number of items which have been applied to a suspense account in between a set of dates so for example i may need all items placed into a suspense account in the month of october. i record all items in a list so i do have a list of dates i just need to know how to count them and keep a running total as i need to feed it into another spreadsheet. I will also need to sum these the format of the spreadsheet is below -
******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Book4___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2K2B3K3B4K4B5K5B6K6B7K7B8K8=ABCDEFGHIJKL1MOPO/S Date AppliedValue DateRemovedValue AgeCompany Name2Cheque 01-Nov-08(15.55) 01-Nov-08 15.55 0abc3Direct Receipt 01-Nov-08(109.00) 01-Nov-08 109.00 0def4Cheque 02-Nov-08(50.30) 02-Nov-08 50.30 0ghi5Cheque 03-Nov-08(23.48) 03-Nov-08 23.48 0jkl6Direct Receipt 03-Nov-08(2,236.46) 03-Nov-08 2236.46 0mno7Cheque 03-Nov-08(212.11) 03-Nov-08 212.11 0pqr8BACS/CHAPS 04-Nov-08(58.75) 04-Nov-08 58.75 0stuSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a sheet named "XYZ Activity" with meeting dates in column B starting with cell B4. There are duplicates in this list, that should only be viewed as one meeting. On another sheet, each company that attends the meeting has a specific join date found in Column C (C4 is the first company start date). I am trying to figure out an equation that will count how many meetings the company could have attended. I already calculate the total meetings they have attended using either of the following equations:
The attached workbook has dates in column C, although some of these dates are just strings.
I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached): ...
I have a list of patients with different categories. Count values of HBA1c which are less than 6.5 and those greater than 6.5 ONLY and only for those of the Category named "PreGDM" but not for other categories.
I want to count the number of times North AND Win, North AND Lose etc.
Can anyone provide me a way of doing this. I dont want to have to use B2&C2 for example. I know I could do this and then use an IF statement (I think).
I have 2 column: item and quantity. I need to count number of items. In column "a" some item are more than one time. I need something like SUMIF function but by using vba. open attached file
2. I need to count how many dates fall during the week using the ending dates. Weeks ending 1/06/07, 01/13/07,etc L2:L53, the dates to count are L63:1600.
I have a two massive spread sheets in which I am comparing items from one with the other and changing in one if the other doesn't match the first. I already have reduced the processing time from 6 hours to about 2 minutes by rewriting the macro to use autofilter to make the changes. The problem I have now is that I need to keep some metrics on the changes i.e. when I apply an autofilter, I need to be able to assign a variable to the number of items actually shown after autofiltering so I can add it to a variable that is keeping track of total number of changes. I know that outside of VBA, in regular excel use, the status bar displays # of total# found during autofilter, so is there a way to tap into the program and pull that number during execution of a macro?
I'm new to the forum - I've done a search but cannot find the answer to my problem.
I work in a hospital and have a list of consultants who refer for a particular type of scan over a period of time. Each time the consultant refers their name is added to a list so I get a list like this: Dr Smith Dr Smith Dr Jones Dr Smith Dr Paeker Dr Paeker Dr Jones etc...
I am working with VB 6.0. So far I have been able to create my recordset, opened excel spreadsheet and imported data to the spreadsheet using the recordset. The spreadsheet has five fields and each field has multiple items as in the example below.
I need to make a pivot table with data from 2 columns. The data has columns named Unit Type and Name. There are 9 different unit types and over a hundred different names. I need to find a way to make a table that counts however many times the name VACANT shows up for each unit type. Is this possible using a pivot table?
I have this work sheet with several formulas in columns Z to AD. All of them highlighted red work fine as for as I can tell. I am stumped with the one needed for the cell highlighted yellow AD2. It should count all the dates in AD1 that are Requested Changes Made and/or Rejected in Column "M". AD2 is a total of today minus 8. Equipment Change out - TEST.xlsx
Can someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).
Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)
I would like to count items that appear in a list that will also appear in several rows. The list is also in no particular order. I have attached an example which may make it clearer than a wordy explanation.
I've an excel sheet with 1000 rows data. I need a formula to count number of unique items in a column with a condition. Please find a sample data sheet.
I've tracking list that has ordered and delivered items for a given month on weekliy basis such as Week1(W1), Week2(W2), etc. I need a formula to count number of unique item delivered on this month.
Item 1 to item 8 delivered and item 10 also delivered in week 4, So totally I need to get the result as 9.
I was trying to use "countif" for validating and counting each row, it's throwing value! Error =1/(COUNTIFS(A:A,A2, c2:c23, ""&0)) i.e., = 1/countifs(unique items from column A, then nonblank cells in column C) but it's giving Value error..
I just need to count the unique item with another condition.
I have a table of data, from which I have created lists for “items created within a period” (the items are paintings on canvasses).
Each list displays which canvasses were completed within 2 dates.
My main data table is large (over 1000 lines and growing) and I need to extract the total quantity of each canvas size that appears in each period list.
My sample attached shows sample data, with corresponding lists of canvasses created within each time period, together with the result I am looking for (shown in red).
is there a formula that gives a true or false statement upon searching a list (ie A2:A6) and finding multiple entries? like, when i search A2:A6 and there are two fields that contain the word "hello", is there a function to say "true, there are multiple entries of the word 'hello'"
