Formula To Reset A Count Based On Cell Value
Feb 2, 2009
I've found a # of threads on this subject but have been unable to find the function I need.
I've got a spreadsheet (attached) to calculate vacation time, but I can't figure out how to make the used time reset on an anniversay date. For example, if Employee 1 has earned 80 hours of vacation but only used 40 by his 4 year anniversary date, how do I make unused time roll back to 80 the day of his anniversary?
Here's the structure: 2 Personal days given Jan. 1, then 2 more on July 1 to employees here at least 6 months. Those whose 6 months fall after or between are awarded 2 days on their anniversary then accrue as normal. Unused personal days are lost at the end of the year. Vacation time is 40 hours after 1 year, 80 hours at 3 yrs, then 120 hours at 5+.
View 9 Replies
ADVERTISEMENT
Apr 3, 2007
I have a formula that counts total number matched items. I need revise it so that the count is never greater than 9. I've been trying several ways - but never get the right end results. So if I have 12 items, the answer will show 9 for those rows, then the next set of rows would show a count of 3. Here is the formula that gives me the count
=SUMPRODUCT(($J$4:$J$4032=J406)*($B$4:$B$4032=B406)*($K$4:$K$4032=K406))
View 9 Replies
View Related
Mar 10, 2009
I am trying to build a spreadsheet that tallies hours worked and hours available for the next day. The key to this spreadsheet is the number 70. A worker can accumulate no more then 70 hours in an 8 day period, however after any day with no hours worked the rule resets to 70 hours available again. I have attached a sample spreadsheet so you can see how this should work.
View 9 Replies
View Related
Aug 7, 2012
The formula I found is close but I need it to count, not sum.
=SUMIFS($C$36:$C$51,$B$36:$B$51,$B36)
Example data: [972 1] [972 2] [972 3] [214 1] [214 1] [765 1] [765 2]
I want the formula to count the different numbers in the second column based on the first column. The results should show, 972 equals 3, 214 equals 1, 765 equals 2.
I am trying to separate this out but it keeps putting this paragraph.
View 3 Replies
View Related
Jul 18, 2013
I am working on some code that I would like the user to be able to re-run should the underlying data in the adjacent tabs change. My existing code is shown below
Code:
Option Explicit
Dim wb As Workbook
Dim ws As Worksheet
[Code]....
I'd like to be able to remove all data on the ALL tab so that when the UsedRange.Rows.Count is run again on that page it will only see the header row. In the past I've deleted or cleared data from the sheet and the Rows.Count still counts the rows which have no content in them.
View 2 Replies
View Related
Apr 4, 2014
I require calculating difference between two columns. (Actual and Estimated).
Difference between these two numbers are calculated in third column, but it is reset every time actual number is non-zero.
Scenario:-
-Estimated Numbers are always followed by an actual number.
-After actual number is obtained, estimated numbers till that date will be added and deducted from actual number.
-After this, again estimated number will be added till next actual number is obtained for deduction.
If estimated number is not followed by actual number, then no need for difference calculation.
View 3 Replies
View Related
Feb 11, 2014
I have a sheet which shows values by month across several years (sample attached). There are two options in cell A2, 'Alive' and 'Dead'.
I'm trying to find a way, either using a macro or not if it can be done without so that, should the user select 'Dead' from cell A2, that all the values in B2:M2 and O2:Z2 are set to zero. However, I need some way of remembering these values because there may be instances when the user then decides to change Cell A2 back to 'Alive' (and they still need to be able to change the value in any cell at any time) and if they do that, I need the numbers shown in B2:M2 and O2:Z2 to be restored to their last set of values. I assume they somehow need to be stored in an array, but I have no idea how to achieve this in reality.
It should be noted that the same will apply to every row in the sheet of which there will be around 3,000, so this needs to work on a row by row basis and there will be 4 years worth of data, rather than the two on the attached sample.
View 2 Replies
View Related
Jan 1, 2008
Have a sample file attached : sample2.xls
What I want to do is - Column H shows my current position, and column L shows the p&l.
I would like excel to calculate the p&l for each position, and put the results in column M.
For example: H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit.
View 9 Replies
View Related
Jan 1, 2008
Have a sample file attached, and what I want to do is as follows-
Column H shows my current position, and column L shows the p&l.
I would like excel to calculate the p&l for each position, and put the results in column M.
For example,
H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit, and I have no idea how to do it
View 9 Replies
View Related
Nov 10, 2009
I have a summary sheet with a table and it needs to show how many people have 14 or more hours available based on a few reports. This is split by different levels (i.e Manager, Senior Manager etc) and various different areas.
The reports are placed in seperate sheets and i need the table to be automatically updated via either a formula or macro, rather then the manual process of going through each sheet and having to filter and then count.
View 13 Replies
View Related
Apr 30, 2014
Is there a formula that can count based on dates?
Here's the scoop.
I created a spreadsheet which I would like for it to track trainings based on days, I added an =COUNTA(A1:A100) formula which counts the amount of people trained but what happens is that it counts people trained all week long. I want it to be able to differentiate days and display the amount of people trained per day , Ex. 4 on Mon, 5 of Tue , 6 on Wed, etc
View 1 Replies
View Related
Jul 1, 2009
Trying to create a dynamic costing spreadsheet, where the user has to make multiple selections from many dropdown lists, all of which end up showing a total cost.
So far I have managed to create relatively dynamic lists, so that the choice in one cell will dictate what options are available in the next.
But if a user selects an options form all the dropdowns, and then decides to go back and modify a previous option to something else, the effected dropdown lists do not change until you click on the drop down arrow again and see the options available have changed.
Let me try and simplify...
'Menu 1' has two option: 'A' and 'B'.
If 'A' is chosen, then the next menu, 'Menu 2', will have options '1' and '2'.
If 'B' is chosen from 'Menu 1', then the options in 'Menu 2' will be '3' and '4'.
Lets say the user chose 'A' from 'Menu 1' and then '1' from 'Menu 2'... AND THEN went back and changed their choice in 'Menu 1' to 'B'....
'Menu 2' will still be displaying '1', even though the options are only '3' and '4'.
Can I make the dependant menu's either reset or blank or highlight to show that a change made somewhere else has made them currently invalid
View 10 Replies
View Related
Dec 7, 2013
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls
View 1 Replies
View Related
Jun 27, 2012
I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.
I need to know how many EE's are listed in column B if CR is on the same line in column A.
View 6 Replies
View Related
Apr 15, 2014
I'm using this formula to count how many times the date in column C and the text "WON" appears in column I and it falls within the month & year that is in Z65.
=COUNTIFS(INDEX(C:C,$AL$15):INDEX(C:C,$AL$17),">="&DATE(YEAR(Z65),MONTH(Z65),1),INDEX(C:C,$AL$15):INDEX(C:C,$AL$17),"
View 2 Replies
View Related
Jul 16, 2014
I am currently working on a workbook to have employees fill in data on what tasks they compelted for the day, and how long it took. There are 5 colums (for this purpose) Task, Description, Time, Required to complete, Completed. The tasks are predefined and listed out in each row. There is also a space for employees to select the date they are entering the data for.
I would like to have a macro that is linked to a submit button and when pressed populates this data into a database on another sheet. This database is split into two sheets (1 with time and one with tasks required/compelted). Each sheet has the list of tasks going down a cloum on the left, and dates along a row on the top. When the employee presses submit I would like this data to popuplate in the coresponding date and task fields and then reset the form on sheet 1 to all 0's.
View 1 Replies
View Related
May 3, 2007
I know there's a pretty compley formula out there that counts the occurence of say Fridays in 2009 - does anyone have this? I had it before in a file but ranged valued the results showing the count of each day of the week for each month in a given year.
View 9 Replies
View Related
Jun 1, 2007
I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.
Situation:
I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this
jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET
and I want to have a count of how many RET's there are for Initials JP ect
View 6 Replies
View Related
Dec 5, 2007
I have an excel spreadsheet which calculates the count and value of line items based on a report. I use the sumproduct function to calculate this as it involves multiple criteria. The formula is use is
SUMPRODUCT(('Report 50'!$C$2:$C$64992= Dashboard!$B172)*('Report 50'!$H$2:$H$64992=Dashboard!G$2)*(LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")*('Report 50'!$L$2:$L$64992=15))+SUMPRODUCT(('Report 50'!$C$2:$C$64992=Dashboard!$B172)*('Report 50'!$H$2:$H$64992=Dashboard!G$2)*(LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")*('Report 50'!$L$2:$L$64992=19))
I think the possible error is coming out of this criteria (LEFT('Report 50'!$S$2:$S$64992,14)="credit transfe")
Out of one particular field i am picking those line items in which the first 14 characters are "Credit Transfe", if i eliminate this criteria i dont get error.
But this is the main criteria for my calculation. Most of the time i dont get any error , but sometines i get the # Name Error? When i double click on the cell it goes to one column. I dont know what to do from there.
View 2 Replies
View Related
May 28, 2009
i have cells A1 to A10 filled with numbers
i have cells B1 to B10 blank
what i need to do is when something is typed in cell B1 - A1 to A5 are reset to zero and when i type something in cell B2 - A2 to A6 reset to zero.
I have tried to do this as a range ie
A1- A10 have numbers in B1 - B10 copy these numbers and reset if anything is typed in C1 with this formula =IF(C1:C5="",A1,"0") what it should do is reset B1 to zero if anything is typed in C1-C5 but only when something is typed in C1 does this work ..
View 9 Replies
View Related
Jul 24, 2009
I have a table that is say 5 colums wide. The top row in the table are just labels (Number, Height, Width, Density, Mass for example)
The rows under that row would be filled with manually inputed data (except for "Number", this would automatically fill down)
The last row of this table, however long it may be (however many datapoints) has data that sums and averages the data above it (average height, width, density, etc)
So if I have say 10 samples, I need a total of 12 rows, 1 for the header and 1 for the footer and ten for the samples.
What I would like, is to have a cell that I input the number of samples into (say there are 53 samples, Cell A1 would say "53) and the table with the data would automatically adjust (insert or delete rows, with the cumulitive data adjusting appropriately)
View 9 Replies
View Related
Nov 14, 2008
if there is a way to reset the last used cell without saving? I have macro first brings in about 30,000 rows of data, but deletes most of it before it finishes running. This works fine, until the third time I try to refresh the report, when Excel won't let me insert another 30,000 rows of data because it would push the last used cell off the bottom of the report and that's apparently not okay, even if the cell is completely blank.
I have it set to automatically clear the blank rows and save when the report opens, but that doesn't help if they run it too often before that happens. I don't want to do a background save because they may have made changes that they don't want to save.
View 9 Replies
View Related
Jan 12, 2014
I want to display the count of particular company in template sheet based on the data in sheet "Total".
View 4 Replies
View Related
Mar 20, 2013
I am trying to establish whether there is a excel formula which will allows you to just to count the cell with the same cell colour. I have made the below listed example to illustrate what I am trying to achieve:
A B C D E11/03
12/03
13/03
14/03
15/03
Total
[Code].....
In the there are total of two green colours, and one red colour to be counted.
View 3 Replies
View Related
Jul 24, 2013
I just can't seem to get the syntax correct for setting or resetting a cell value using the OnTime schedule function. It looks like the cell value changes randomly and after several cycles it just runs non-stop very, very quickly and I have to use the ESC key to break out of the loop.
Here is my code:
VB:
Sub RunOnTime()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "RunOnTime"
If Cells(1, 4).Value <= Cells(6, 2).Value Then
Cells(1, 4).Value = Cells(1, 4).Value + 1
[Code]...
View 1 Replies
View Related
Nov 12, 2013
My sheet has a Data Validation drop down menu "Department", where the user selects a Department. Based on this selection, another Data Validation cell "Team Leader" gives a list of Team Leaders to choose from. This works perfect. Change the Department, you get a different list of names. The problem is that if I have both a Department and a Team Leader selected, and then I change the Department selection, it keeps the Team Leader cell filled with now invalid data (the Team Leader from the previous department). If I then click on the Team Leader cell, it has the correct names listed to choose from, but until I do that cell retains the old information. Is there any way to blank the "Team Leader" cell, or push data to it (I've included a Choose a Team Leader entry in my list of people), when I change my Department selection?
View 9 Replies
View Related
Jan 19, 2008
I basically have four cells
c8 = goods in (new components in)
c9 = kanban stock (current qty in stores)
c10 = current stock (=kanban stock minus allocated)
c11 = allocated (number on order)
What I would like to be able to do is enter a figure in c8. This would then start the macro to update c9,10 and 11.
The problem I have is that if i enter a figure into c8 sure it alters the others if i do a quick =sum formula but does not reset. Therefore if I put for example 6 in cell c8 and then want to add another 8 all other cells alter accordingly. I want all cells apart from c8 to update and hold the figure after c8 has been entered.
View 12 Replies
View Related
Jul 23, 2013
I just can't seem to get the syntax correct for setting or resetting a cell value using the OnTime schedule function.
It looks like the cell value changes randomly and after several cycles it just runs non-stop very, very quickly and I have to use the ESC key to break out of the loop.
Here is my code:
Code:
Sub RunOnTime()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "RunOnTime"
If Cells(1, 4).Value
View 5 Replies
View Related
Jan 19, 2007
Is there a way, either in Excel or using VBA, to reset xlLastCell without having to save the workbook?
View 4 Replies
View Related
Jan 28, 2013
I have an issue log containing (separate) columns with dates, numbers and text.
One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)
I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).
After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.
So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".
After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.
View 3 Replies
View Related
