I wish to count the number of entries in a workbook based on the entries in two columns. The columns are for example H1:H100 ( date) and L1:L100 (status). I want to the number of times the date in H1:H100 is over 30 days old, as well as L1:L100 = 'Started' for example.
View 2 RepliesI'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
I am putting together a worksheet with all my teams tasks. I have columns A:U and Status is column "M". What i want to do is when ever i select Complete from the list in Column "M" i want to the row to turn Green.
I tried the following:
Selected the Range
From Conditional Formatting menu selected Manage rules
Selected new rule then Use formula to determine which cells to format
formula i used =$M$2=complete and selected green from the format menu
BUT nothing is happening. What am I doing wrong.
BTW the Values in Column are Complete,Development, Not Started Research, QA/UAT. If I can conditionally format based on these selections that would be even better.
I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).
What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.
Picture:
Column C Column D
111111 M
111111 M
111111 M
[Code]....
Currently calculates: M=9, F=2
Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.
Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))
modifying the following from deleting rows older than 365 days to delete rows older than 12 months. So I can keep a rolling year by month.
Sub DeleteOldSR()
Dim x As Long
Dim iCol As Integer
Application. ScreenUpdating = False
iCol = 7 'Filter on column G (Create Date)
For x = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1
s = Cells(x, 3).Value
If s Like "Closed" Or s Like "Closed w/o Customer Confirm" Then
If Cells(x, iCol).Value < (Date - 365) Then
Cells(x, iCol).EntireRow.Delete
End If
End If
Next
Application.ScreenUpdating = True
End Sub
I tried modifying the IF statement using month with the following but it seems to delete all months < and is not year dependant.
If Month(Cells(x, iCol).Value) < Month(Date) - 12 Then
In one spreadsheet, I want to have a command button that will hide all rows where the date column (column A) shows a date older than one week from today. When this button is clicked again, all rows will unhide again. Preferably the Command button title would change to reflect whether it is on the hide or show cycle (for example "Click to Hide all older than one week" and then "Click to Show all events") .
View 9 Replies View RelatedI've got two worksheets. One contains data from a query, the other worksheet should count rows in the queryresults based on two criteria. See example.
I tried doing it with sumproduct in the normal variant and the array-variant...
From a base table, I need to create a new table listing only the unique combination of 2 columns and their individual sum. Example:
Base Table:
ITEM-----Loc-----QTY
Pen------School----5
Pencil----Office-----9
Binder---Office-----9
Pen------Office-----6
Pen------School---15
Binder---School---12
Pencil----Office----10
Binder---School----6
Pen------Office----11
I need to have a table resulting to the following:
Item-----Loc------Qty
Pen------School----20
Pencil----Office-----19
Binder---Office------9
Pen------Office-----17
Binder---School----18
how can I count the number of not empty rows(and for example too many) using do.. until loop kind. I am searching on the net for about 24 hours but I couldn't find it.
View 5 Replies View RelatedNeed a formula for counting the number of occurences of a month & year in a date column? The spreadsheet is looking at items raised in any given month e.g. all items raised in Dec-08.
View 3 Replies View RelatedI have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 16:01:00
Days Late = 2
I'm using a sumproduct forumla to count rows based on specific data in multiple columns. So if column A equals 1 and column G does not equal 6 and column M equals 4 then count that row. I know how to do this. The problem I'm having is that I want to count column A if equals 1 or 2. I tried adding an OR comand in with my sumproduct but it doesn't seem to work that way.
View 4 Replies View RelatedI have one column with names and another with date ranges (JAN06) etc. I
want to count the number of times a name occurs within a given date range.
I want to count the number of times a certain value is found in columnB, only for unique values in columnA.
I have made a simple example file, attached. Column B shows Status which can be Active, Current, Dormant. Column A holds Item names. I want to know how many Active Items I have, but an Item can appear more than once in ColA, so I want it only to include in the count the unique ColA values.
I have some vba code that currently copies a status table out of a worksheet, into a temp file for an email. The status table in excel has a column D which can have different status. I want to select everything in the table except rows that are 'Descoped'. Currently my code looks like:
Set rng2 = Sheets("Execution Status").Range("B2:F420")
This picks up everything without looking at the status. I want to change it so it doesn't pick up the rows where Column D contains 'Descoped'.
In pseudo code I would describe it as:
Set rng2 = Sheets("Execution Status").Range("B2:F420") where value in D5:D420 is not equal to 'Descoped'.
I have tried:
Set rng2 = Sheets("Execution Status").Range("B2:F420").Value "Descoped" and nothing gets selected.
Formula which will count unique values in column A based on condition in Column B which "y" .....
Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....
1. If I enter a date in B3, then D3 will be S, and if I enter date in C3, D3 will be left blank while E3 result is D (Done). To have a clear view, I have attached the excel sheet.
View 3 Replies View RelatedCount value in date and status criteria as in attached file.
UNIQUE VALUE COUNT .xlsx‎
I need to create a formula that will calculate the status of a customer based upon an input code and the relevent date.
The attached workbook is very simplified but should help.
Sheet1 contains the date of input (col A) for the client (col B) plus a code in column C to help ascertain the initial status of the a/c.
In Sheet2 (col B) I need a formula or possibly a VBA solution that will create a commission status for clients with 'N' in column C of Sheet1 which is current for 8 weeks after the input date in Sheet1. Thereafter this status then need to show say an 'S' for standard commission.
This result will be linked to the date shown in B2 on Sheet2. Just to make it clear, I only need a letter as the result to trigger off the actual commission calculations.
I have excel in the below format.
Sheet1 ABCD1NameDescStatusSpend2raviGood boy1103kumarAve3104raviGood boy1205raviGood boy2306kumarAVE1407sureshMED1508subMED3609raviGood boy27010raviGood boy38011raviGood boy120 Excel tables to the web >> Excel Jeanie HTML 4
I need sum of value based on name and status.
And also i need Descrption also.
It is possible in VBA.
Sheet1 FGHIJ1NameDESCStatus 1Status 2Status 32raviGood boy50100803kumarAVE40 104subMED 605sureshMED50 Excel tables to the web >> Excel Jeanie HTML 4
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014
28/1/2014
28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
I have a user form we are using to transfer data into an excel spread sheet. When transferring the date from a text field it is formatted as general, I need this to be a date format DDMMYYY. The cell needing formatting is N2
View 5 Replies View RelatedI have a query around dates and current project status.
I1 Contains the Current Date
C4 Contains Due Date
C5 Contains 4 Possible Selections. Not Started, In Progress, Awaiting Feedback,
Complete
What I want the formula to do is.
IF Current Date is greater than Due Date AND Current Status is either Not Started or In Progress then return Overdue.
I've written it as follows, but it doesn't seem to work?
=IF(I1>C4,IF((C5="Not Started",IF(C5="In Progress"),"Overdue",""),""))
Here is a table
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail
[Code] .....
Here is some context
Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).
In this table the number of tickets that were:
submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1
Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4
[Code] ....
What I need is a formula that will produce this result.
I have a survey template with several options buttons (made from Form toolbar and grouped by Group Box).I would like to have a check box that would activate these option buttons for specific question if unchecked and vice versa.
I have copy & paste below formula in VB, but is shows 'Run time error 424' & when I Debug it shows 2nd line highlighted by yellow colour.
VB:
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then [code]....
I'm having difficulties setting up a spread sheet which needs to be reported to my management weekly.
We follow a 6-stage buy off procedure and within a spreadsheet I have smiley face icons in each of the stages as they're reached.
For example, someone could be at the second stage of the buy off and all going according to schedule, I've got 3 emoticons through Wingdings text - J, K & L (paste in windings to see icons if you wish). There are several projects with this 6 stage buy off procedure and as lots of people access this shared document to edit weekly there are lots of different statuses.
I'm wanting to have a custom view with all the 6 stages hidden and then a separate column with "Status" as the header - this cell needs to display the most up-to-date emoticon of each project but using a normal IF/OR combined command only gets me to control 1 column. I have attached a spread sheet example with most content removed due to beingTEST IF COMMAND.xls strictly confidential material.
is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.
View 7 Replies View RelatedAny way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed
Disposal Order
Latest Decision date for D.O.
06/05/2014
D.O.001
[Code] ........
I think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:
AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May
If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.