Counting Number Of Rows Where Datediff Of Two Columns Meet Criteria
Aug 21, 2013
I have a range of data and need to summarize some of the data. In the table below is an example of the data. I would like to have a single formula where I can count the number of rows where the end date - start date is < 14 days. I would prefer not to add an additional column for the datediff value. Is there a way to do this with either arrays or named ranges?
Name
Start
End
Other data
Verizon
1/10/2010
1/25/2010
AT&T
3/2/2010
4/14/2010
BP Formula.xlsxI'm not sure what formula to use or if Excel can do this. I want to use it to determine number of blood pressures in a certain range. So if I use column A for SBP (systolic BP) and column B for DBP (diastolic BP). I want a formula to tell me if column A is between 140-159 or column B is between 90-99. I can get that part, but what I'm having trouble with is it counting each row twice if both column A and B meet the criteria, whereas I want every row (person) to only be counted once if either column A OR column B meet the criteria. (See attachment)
I would like to pull all rows and columns from a table and copy them to a separate sheet. I would like to pull the data if a certain value is present in a particular column. Essentially, it is the same effect of filtering the data but I don't want to have to keep filtering and copying specific cells and pasting them into a different sheet if I need to change the raw data.
Example: I would like to copy all ids, store, and partner information IF the partner is "X". I would like to put this information in Sheet A.
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted. The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26.
Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field.
Every other line is the planned production and the others are filled in after as the actual production I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day.
Here is my problem- if a station is running two different products it gets counted twice. I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines. Attached is an example of what I have with the formula and below that is what I am looking for
I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"
I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.
The following formula was able to give me the count of cells "N/A" =COUNTIF('Master Data'!$UA59:$ALZ59, ">0")
and this gave me a count by Month/Year =SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))
When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.
I have a sheet with "Yes", "No', and "N/A' in most of the Columns. (E - Q) and about 100 rows of data.
I only want to count the number of rows that have "No" in column E and Yes in all the others. If "no" occurs in any of the other columns in that row (D-Q) I dont want it to be part of the count.
In my Macro , i add a worksheet and depending on certain conditions i decide whether to populate it or not.
At a later point of time i am checking if the newly created worksheet is empty or not using the following check :
If ActiveWorkbook.Worksheets(ISheet).UsedRange.Rows.count <> 0 Then
Now what i have noticed is that even in cases where the "ISheet" is empty the above condition evaluates to true for the first attempt. I cannot understand why ?
let me know if the query is ambiguous and i will post the code snippet
I have several columns in an excel sheet which contain values (eg. Names). The entered names can be unique or already exist in the column. I need to find out (using a function or macro) how many different names were entered. Duplicates shshould be ignored in the count.
On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?
Basically, need to count rows that have 5 columns of data in it. If all 5 columns within a row have something in them greater than one character, that row needs to be counted.
I have a filtered list of data expanded to "all" in each column. One column is "percentile" (25%, 50%, 75%, etc).
Search down the "percentile" column until you encounter 25% as an entry, then sum a number in the same row from a different column with each 25% entry you find.
counting some room numbers that meet a specific criteria. For example:
Let's say i have room numbers
1233 1234 569 2148 3422
I need to count the room numbers which the last two digits are greater than 40.
So far I have tried to separate the last two by using the Right formula but then when I try to use countif, it doesn't work as I guess that the result of the extraction comes back a a text value
I attached a spread. On the data tab, I have 2 criteria that I would like to use to move rows to another sheet. Columns U and W. An example would be that all rows that have a coil in Bay "B" and are allocated would go on the "B Allocated" sheet. I would also like this to automatically update when I delete the data in the data sheet and put new data in.
I have a spreadsheet with 3023 rows and columns A-L. Here's a small sampling of Column A (with heading "sku" included, copied exactly as it appears in the spreadsheet):
A sku 3102-0400-100000 3102-0400-200000 3102-0500-100000 3102-0500-200000 3102-0600-100000 3102-0600-200000 3102-0700-100000 3102-0700-200000
I need to delete every row in which column A includes the text "200000".
Although the above sampling shows "200000" appears in every other row, that is not the case in all 3023 rows.
I have a spreadsheet which contains data for crime incidents occurring in a month. I have created a worksheet which uses "IF" statements to pull in data that occurred within the last 24 hours only. I would like to create a report that can be printed which has a crime category and lists all the incidents for that category below that occurred during the day. The issue I am having is that all "IF" and "LOOKUP" statements are line for line and I want to be able to skip blanks or where it is not the category in the title.
I have a workbook with many (25) worksheets which all have the same structure, column headings, etc. but vary as to the number of rows. I would like to search all worksheets in workbook and copy to worksheet "120" only those rows where column C is "120" and column E is "1-00053-".
Ideally, input boxes would be used to enter these criteria so that it can be used for different scenarios in which these values will vary.
I have a sheet of data. The data starts in row 4 and can be variable in length. I want to delete rows that do not meet a specific criteria, for example a location, Leeds, which is in column "i" of the table. I have written the below but it seems to delete data from row 1 rather than row 4 and repeat down each row
Code below
Sub deleterows() Dim i As Integer Dim intCounter As Integer Dim strLocation() As String
p = Range(Cells(4, 6), Cells(4, 6).End(xlDown)).count
I would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.
I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting
Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'
Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)
I have a list of brands in one worksheet that are abbreviated (Brand 1 = "AB-"). In another Worksheet I have a list of products that start with various brand abbreviations (ex: AB-12345, BP-12345), and in another column on the worksheet I have codes that represent certain characteristics of that style ("1"=flat shot), "2"=shot on model, etc). Now what I want to do is count how many instances I have of products that begin with "AB-" and have a "1" in the other column so I can get a count of how many flat shots i have to do for that brand.
So far I am using the below code to get a TOTAL count of products that start with "AB-", but i cant figure out how to write it so that it checks additional criteria in another column.
A3 contains the text "AB-" and 'photo list'!G2:G5001 contains the list of products that may or may not contain the text string "AB-". Column S (not shown in this code) contains the codes for how to shoot. Hope someone can make sense of this and give me a hand.