How do I count between two dates with two criterias?
Col B = dates
Col D = Results (in this case "Car accidents") dropdown cell = A100
Col G = RRV or aircraft (Type of transport) dropdown cell = B100
The data is found on Sheet("Orders")
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!
In Cells B2:B100, i have dates that which have been entered using a combo box (the dates type is for e.g. 14th March 2010 format)
I want a formula that will count the cells that have dates between 1st April 2010 to 30th June 2010 in cells B2:B100
Also, I would like a formula that counts weekend dates between 1st April 2010 to 30th June 2010?
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:
=COUNT(1/FREQUENCY(IF('XYZ Activity'!$C$4:$C$4000=A3,IF('XYZ Activity'!$B$4:$B$4000<>"",'XYZ Activity'!$B$4:$B$4000)),IF('XYZ Activity'!$C$4:$C$4000=A3,IF('XYZ Activity'!$B$4:$B$4000<>"",'XYZ Activity'!$B$4:$B$4000))))
=SUM(IF(FREQUENCY(IF('XYZ Activity'!C$4:C$5000=A4,IF('XYZ Activity'!G$4:G$5000="Yes",MATCH('XYZ Activity'!B$4:B$5000,'XYZ Activity'!B$4:B$5000,0))),ROW('XYZ Activity'!B$4:B$5000)-ROW('XYZ Activity'!B$4)+1),1))
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 am trying to filter data based on more than one criteria (8 to be precise). I have some data in one worksheet and i need to transfer it to other worksheets depending on certain criteria. for example if cell A1 has A or B then it should go to "temp1" spreadhseet, if A1 has C,D, E, F, G or H then it should go to "temp2" worksheet etc.
Is there a smart way of doing this rather than writing a number of with statements using 2 criterias each and hence copying data in more than one attempt (and thus slowing down the macro)?
I did think of using creating a dummy column, then using If statements to write True or false in that column, using true & false to filter and copy the data and then finally deleting the column. but as i understand i can not have more than 7 nested if statements but i have 8 criterias.
I've got a line of code where the excel sheet creates a report by picking up certain values from the cell. One of the code is as follows
If (strGroup Like "Computers*") Then
GiveTo = "Youngsters"
where Youngsters is a emailgroup created for the report. I want to add certain extra things to this strTask Like "Help*" Then GiveTo = "Volunteers".
i need to know, how much people belongs to the number in Colum A - if in
colum C is written "ISM".
A B C
1 1 Meier ISM
2 3 Huber ISM
3 2 Schmitz UPA
4 2 Mayer ISM
5 1 Mueller UPA
6 1 Hase ISM
I am having trouble creating a formula that will do what I need it to do. In C4 the I already have a formula that states if B4 is greater than 94.99% then it will equal 5. What I need it to do is if it is equal to or greater than 95% then =5. Also if it is equal to or less than 94.99% - 92% =3 and if less than 91.9% =1. Here is the worksheet I am using....
View 15 Replies View RelatedI'm trying to modify this code in order to do the following.
I want to choose first column and then the criteria for filter, then i want to choose a second column and criteria to filter the remain values from the first filter.
Here is my
Sub Filter()
Dim Myrange As Range
Dim CriteriaVal As Variant
Dim CriteriaVal2 As Variant
Dim KillColumn As Integer
Dim KillColumn2 As Integer
Dim ActiveColumn As String
Dim AC
Dim LastRow As Long
Dim rng As Range
If I have to match MAX from Row 1 and MIN from Row 2 and get the EXACT MATCH as well as more than 1 Match?
I have 3 Rows and 4 Column Matrix
from A1 till D1 contains values as 10,20,50,40
from A2 till D2 contains values as 30,20,10,40
from A3 till D3 contains values as TOM,****,HARRY,JONES
THen my answer would be HARRY, however if there are more such combinations present then I would require a list of all of them.
Ex2 For Multiple Lookup
from A1 till D1 contains values as 50,20,50,10
from A2 till D2 contains values as 10,20,10,40
from A3 till D3 contains values as TOM,****,HARRY,JONES
If column A is greater than B and column C is blank then Column D should show value as 1.
ABCD
15121
Similarly if column B is greater than A and column C is blank then Column D should show value as 2.
ABCD
12152
Can I put both the formulas in one column so that I get the value as 1 or 2?
I have for example database in three columns - A, B, C:
__A_______B_______C
Order1___Item___99-99-99
Order1__________10-15-78
Order1___Item___88-88-88
Order2___Item___10-18-25
Order2___Item___10-15-25
Order3___Item___10-15-25
I have two criterias: Order1 and Item
How to find in DB sheet these data and copy in to other sheet?
I hope get this result:
99-99-99
88-88-88
I need to use a not equal to operator to apply filter on a column. Something like:
Fc = Array("BA-DIRECT", "DAFC", "WSS", "LISS-East", "LISS-West", "GSO DRO")
ActiveSheet.Range("D:D").AutoFilter Field:=4, Criteria1:="" & Fc, Operator:=xlAnd
I need to filter all values which are not equal to "BA-DIRECT", "DAFC", "WSS", "LISS-East", "LISS-West", "GSO DRO" in column 'D'
The above code is not working.
Column A has a long list of dates in it like this....
9/20/08
9/21/08
9/21/08
9/21/08
9/22/08
9/22/08
9/23/08
9/23/08
9/23/08
9/23/08
I need a formula to count the dates that are the same and display the count number.
result...
9/20/08 1
9/21/08 3
9/22/08 2
9/23/08 4
etc.
Any ideas?
Generally I need to get the total records of Sheet2 with ID validation and that are not blank
<Sheet1>
IDName,Total
101Tony
102Gary
103Barry
104Anthony
105Julia
106Mary
<Sheet2>
IDnameDate
101Tony12-Nov
101Tony2-Dec..............
i have a spreadsheet with the following headings
"start date" - "end date" - "pallets"
20/09/08 28/10/08 20
01/10/08 10/10/08 15
05/10/08 15/10/08 20
05/10/08 11/10/08 18
I then have another table and need to total the pallet quantity by month. Does anyone know a formula where i can have a TOTAL pallet figure by month, therefore showing October as having 53 pallets?
I have a large sheet with about 5,000 records.
Col J contains a date field in the format dd/mm/yyyy
Col AC contains either nothing or "YES"
I need a macro which will ask me for a month and year (mm/yyyy) and then
give the number of YES's for that month found in Col AC
I am working on a report for work with the following formula:
=SUMIF(A5:G8,AND(between 1-1-13 and 1-31-13,"soft cost"),G5:G8)
my formula is "IF A5:G8 IS BETWEEN JANUARY 1st 2013 AND JANUARY 31st 2013 AND ALSO IF IT IS A SOFT COST THEN GIVE ME THE SUM OF THAT ROW"
Pretty much column A contains different dates and column C indicates whether a cost is a "hard cost" or a "soft cost". If the date is within the month of January AND if it is a soft cost, I need the dollar amount in column G summed-up (must meet both criteria - January and soft cost).
I want to create a conditional formatting formula, which marks products currently in stock.
Lets say that I have the following setup:
is there any way for a sumif formula to have multiple criterias? for my case, after the formula checks for a condition, it has to check for another condition before summing up the figures.
this is my current formula:
I've forgotten all the formulas that I learned during college. And that was for Excel 2003; now I'm using Excel 2007. So, hopefully you experts can help me out.
So here's the problem. I need to set up an invoice in Excel but I don't know what formula to use. I'll first describe my invoice and then I'll tell you the problem.
It's a basic invoice where you have customer's info and product's details and prices. So, when I choose a customer's name from a list in a cell, the address and phone of that customer will automatically appear. I don't have a problem at all with this part. And then there's the product details section where you enter a product type, size, color, price per unit, and total price. The price per unit also depends on the customer entered. Each customer has different pricing and I used a discount system for each customer. We basically have a universal price list for our products, but we have different discount percentage for each customer.
My invoice will look something like this: ...
Say i have database (A2:X4), the first 2 row is ID (text), and the last row is the value.
If given, 2 ID that can be looked up to the database, and say the data given is equal to the ID in cell I2&I3. The question asked is how to get the sum value of (cell E4:G4)?
For more clear explanation i have attached the example.
Need to kown if Index and Match can be used if you have to criterias. Below is a glimse of my sheet. The data does come from 2 sheets, I don't know if that is another reason, doubt it, but needed to tell you just incase. I have sales information and I just want a quick view of the sales by associate by date. My 2 condition are the associates id# and the date.
=INDEX(($J$2:$Q$4),MATCH(AND($A2,C$1),AND(N2:N4,M2:M4),0),9)
my range is from J2:q4, my criteria is column a2 Sales asso Id and C1 the date, and match is N2:N4 & M2:M4, witch is the 9th column.
I have managed to make a work queue and lots of other stuff for the model, but I can't get it to take orders in the way I want it. Each order has a order number (from 1 to 100) and the orders come in almost randomly e.g. 3, 5, 11, 2, 7, etc. What I want to do is to take the smallest available order that has not been processed in.
The available orders column and processed orders look something like this:
A B C D
Time, Available, Processed, Start processing
5 2 0 2
10 0 0 0
15 0 0 0
20 0 0 0
25 5 0 0
30 0 0 0
35 0 0 0
40 0 0 0
45 4 2 4
50 0 4 0
55 7 0 5
60 6 5 6
Zero means no new orders or no processed orders. Now the Start processing column should select the smallest not processed order if previous order has been processed.
A have, for now at least, all other problems solved, but can't figure out how to get start processing column check for the smallest not processed order line. I have tried combination of Min and Max functions with If, but it soon requires too many Ifs to make any sense out of it. I also tried the Dmin function, but it wasn't up to the task becouse the model requires ~1000 lines and as Dmin only takes criterias vertically I ran out of columns . So how could find minimum from row one until current row excluding values processed so far and only checking orders available so far?
I have a spreadsheet that i manually edit each and everyday e.g.
A B C
EABGL/UD NDT254892
MRMR/RUS/ELQNS259762
LSL/UW/B LQNS267259
WWEX/UQ bbr263666
LWL/KL/B 270407
MYTCJ/UB NDT271774
LNL/SB/UB HLC - 271955
SMMQD/WT HLC - 269516
EACO/TN/UGBBR257827
NILVA/UC EUi273645
For everything that doesnt equal EM, LN, LW and TH in column A, everything should be deleted in column B.
For the remaining EM, LN, LW and TH, i would then like it to delete / (forward slash and all characters after this) so that this would make my life easier.
I'm trying to figure out the answer to #5 in the word document. I have it highlighted.
View 1 Replies View RelatedI have a string of dates and I want to count how many falls between specific dates.
View 8 Replies View RelatedI track dates of training for my fire department. Training is due every two years I want to know when the date is more than two years old and when it will be two years old in the next six months or less. I have conditional formatting that changes the color of a cell based on two conditions. Condition #1 (Yellow) the date is more than 2 years old, [=TODAY()-B2>730] Condition #2 (Green) the date will be more than 1 year old in less than half a year. [=B2+182-TODAY()<185]. Is it possible to count each occurrence those two conditions with a formula?
View 4 Replies View Related