I have a spreadsheet containing 10,000 + entries.
Each Entry is Dated within Column D2:D10786 in this format - 1-Nov-08 (example).
Lets say i have a cell on another sheet Cell A1 and in this Cell i want it to Count how many Cells contain the dates from Nov-08 in my Date column..
I have a specific issue that I have not been able find in here or on the internet. I need to count entries in one column based on a date range using corresponding dates in another column.I found countif, countifs, dcount, etc but have not been able to get any code to work. I don't know if my excel spreadsheet attached correctly but I can explain it since it's a simple one. Column A is filled with report dates that generally increase from 10/1/2012 t0 9/30/2013. Column B is filled with the number of "AFIS searches." If an AFIS search was not conducted for a specific report then the cell is blank. I need to count the number of AFIS searches between the dates of 1/1/2013 and 3/31/2013.
View 9 Replies View RelatedI 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.
I am trying to count the amount of cells in a column (Col H) containing data against a certain date which is displayed in another column (Col B)
Eg.
Col B................-...........Col H
13/06/09.................FIRST LAST
13/06/09.................
13/06/09.................FIRST LAST
20/06/09.................FIRST LAST
20/06/09.................
So that in the sheet next to it it would appear:
13/06/09 - 2
20/06/09 - 1
The dates don't appear in row order and the sheet has a filter on it so I am unable to select the Col H and just use COUNTA
I have a worksheet where
Column D lists a service offered by a business, such as IMC, TC, Respite, etc
Column M lists how long the customer has used this service for in weeks, such as 4,5,3.5,6 etc
What I would like to do in another column is to count the number of clients who have used the service for 6 weeks or more broken down by the service type i.e. Count how many people have used IMC for 6 weeks or more.
If possible I would also like to be able to count those who have used a service for between 5.1. and 5.9 weeks too, again by service i.e. Count how many people have used IMC for between 5.1. and 5.9 weeks.
I am trying to count the amount of times 4, 5, and "11-5" show up in a column and sum it at the bottom. I tried
=sum(if(c4:c41="4"+c4:c41="5"+c4:c41="11-5",1,0)
As an array and keep getting N/A. So im not sure where my problem is.
I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.
View 3 Replies View RelatedI've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.
View 2 Replies View RelatedUsing the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.
Goal: create a count of unique entries within a defined variable date range
I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.
I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.
It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?
I'm having trouble with a complex Excel formula. I'm calculating child support owed based on the current date coupled with future child support owed. My columns are as follows:
Debt / Future Amount Owed / Monthly Payment / Total Amount Paid
10,148.72 / 83312 / 508 / 1000
On the first of each new month, the Future Amount Owed should decrease by 508. Altering the Total Amount Paid should decrease the Debt column. Progressing another month without altering the Total Amount Paid column should add 508 to the Debt column while subtracting 508 from the Future Amount Owed column.
I have contarct for amount $5000 with a start date of 1/1/2014 - 3/31/2014 (3 months). I would like to equally split my amount based on my number of months between 1/1/2014 and 3/31/2014 ie $1666.66/per month.
View 4 Replies View RelatedI tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.]
The situation:
I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values.
namegender
jones m
martinf
smithf
collinsf
wilsonm
jones m
martinf
hughesm
wilsonm
martinm
smithf
west f
jones m
west f
martinm
The challenge:
In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman!
The correct result: ...
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.
I added a new cell called Track Back on the employee search sheet.
What I want to do is only show variances for the amount of days back selected in the Track Back cell.
For example if I select the last 30 days, only the last 30 days would show up below in the sheet.
I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.
Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.
I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A?
The 4 items that will populate in Column B will always be the same.
I have attached an example to better explain what I am trying to describe.
Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View 8 Replies View RelatedIn the same worksheet, I would like to format column "U" based on the alpha character in column "T" and also format column "AA" based on the alpha character in column "Z". There are three alpha characters that will be used in columns "T" and "Z" ( upper case and lower case), "A" with the format of $ #,000 & "H" with the format of $ 0.00 & "P" with the format of percent %. I want to format column "U" & "AA" prior to entering a value.
Column "T" & "Z" are a pull down menus containing only the values listed above (a,A,h,H,p,P)
I had asked and received an answer regarding the above pertaining to columns "A" and "B" and was given an answer that works, however, today my boss changed the configuration of the spreadsheet and for the life of me, I could not get the code to work using the new parameters.
This may be a basic question but I haven't been able to find the solution:
I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example
0001 a
0001 b
0002 c
0002 d
0003 e
0003 f
I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example:
0001 a
0002 c
0003 f
I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
Example Workbook.xlsx‎
I had an idea that I could use the level of indent in the first column to provide the name for the range. The easiest way to explain is with the example spreadsheet I have attached
Cells C5:C10 show how the naming convention should look like, basically Indent Level 0 returns a prefix (Sheet_Name_Prefix) and its corresponding row entry in Column A. Level 1 should return the last Level 0 name and its corresponding row entry in Column A.
I had a go at the code and it works for Level 0 but I can't get it to put to Level 1.
Sub Test()
'Dimension Variables
Dim RowTitleEntries As Integer
Dim NameLoop As Integer
Dim IndentLevel As Integer
'Set the value of RowTitleEntries
RowTitleEntries = 6
What i want here is the total for a particular id.
View 3 Replies View RelatedWhat I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
I need some code to do the following.
Look at worksheet 1. Find column "Amount". Insert column next to amount.
For all highlighted cells in column "amount" insert text "R" in adjacent (inserted) column.
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View 2 Replies View RelatedI have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A.
Here is what I have but it is counting the dates in col A not the order numbers in B?
I am trying to Count (or counta?) cells in one column if they have data but only if within a date range in another column.
So, in the example below, I would want to count how many cells in column E have data in them, but only if the date in column A falls between Jan 1 2014 and Jan 5 2014. (In this case, result should be 4).
A
B
C
D
E
01/01/2014
[Code].....