Using DCOUNT And Date Based Criteria
May 15, 2009
i'm trying to write a summary page for the data collect on the first sheet and would like to find a way of counting the number of records within certain date criteria (those that are more than 30 days old). now the crucial part is that the records have a second set of critical data, their Status, and i need to find only the records that have a particular status (in this example that is Pending and Open).
if you look at the second sheet you'll see that i can find records that are associated with a specific date, but not with a range of dates; which is particularly annoying because DCOUNT can find ranges of information when using other types of number.
View 3 Replies
ADVERTISEMENT
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
Nov 10, 2009
OK, I have a list of sales that my semester long company has sold. In the midst of these sales we are sellling a versoin of the bumble watches. people can either buy a watch, (which includes a face and a band) a face, or a band.
So I have this giant list and only like 10 watches on there. I need a formula that will count how many watches and faces we sell.
I was initially thinking something along the lines of:
=COUNT(OR('Sales Journal'!D:D="Watch",'Sales Journal'!D:D="Face"))
=COUNT(OR('Sales Journal'!D:D="Watch",'Sales Journal'!D:D="Band"))
Which worked great until I had more than one watch in there.
I can't figure out dcount, and don't know if it's even the right thing to use in this instance. If you want I can post the sales journal..
View 6 Replies
View Related
Dec 12, 2013
Using the dcount function it appears that the criteria is not exact. I have typed in ="IP" but instead of getting only IP. I'm getting cells beginning with IP, eg IPp and IPe. When I use countif I don't get this problem.
View 3 Replies
View Related
Mar 1, 2014
I have created a register that gives the ages of the participants being registered. I need to figure out the number of boys and girls of different age groups. I used the formula =DCOUNT(database,column no,criteria) with criteria having two columns, Gender & Date of Birth. Under Gender was M, Under Date of Birth was >=01/03/2002 [dd/mm/yyyy]. This gave perfect results shwing me the number of boys less than 12 years old. However, wishing to make the formula dynamic I changed it to >=EDATE(TODAY(),-144) which is exactly twelve years ago. This formula gives wrong results and I can't understand why.
View 9 Replies
View Related
Jun 10, 2014
I am using Excel 2010.I have a Advanced Filter that has removed 2 list, with 3 criterias, List ( or DB1 ) is based off 1 item " Pay type ( hourly / Salary )" , then the 2nd List is based off 2 Criteria's Job type , and Less than a number, I am able to do the filter just fine, but i am requried to DCOUNT theses.
here are the ranges for the Default Table: A6:H45 (without headers)
List1 is in J6:Q27
List2 is in J33:Q37
*Both are without headers also*
I am asked to "DCOUNT"
List 1 based (A6:H45,"Type",L30)
List 2 based (A6:H45,"Job Type"+"Salary(has to be
View 2 Replies
View Related
Jul 1, 2009
I need a formula that acts very much like a DCOUNT formula. However, I need to count the numbers of instances where "09007" is in Column A IF the value in Column B is greater than zero OR if the value in Column C is geater than zero.
I would prefer a formula that can calculate this with only the existing data. In other words, I don't want to have to add additional columns with formulas to help get me to the results.
View 9 Replies
View Related
Feb 25, 2014
I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).
View 2 Replies
View Related
Nov 24, 2006
I wanted to solve a problem in excel. For a particular period there will be flow of units on a daily basis, which can be more or less than actual capacity. Flow received will be done on first in first out basis. I wanted to know the completion date for the same in excel. I'm attaching the sample sheet of the same.
View 7 Replies
View Related
Mar 20, 2012
I have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.
This is my table
ABCStyleColorDelivery Date1ABCRed1/1/20122ABC
Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC
Red2/27/20126ABCRed3/1/20127123Blue3/20/2012
and my desired results are as follows:
StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012
The style and color columns are already filled in, I just need a formula for the date column.
View 1 Replies
View Related
Jul 16, 2007
I need to determine the MAX date in a column based on specific criteria. Column A6:A500 = Task Names, Column N6:N500 = Dates (there are some blanks).
What I need is to determine the latest date in column N6:N500 based on which task I choose from column A6:A500.
View 9 Replies
View Related
Jan 3, 2007
I am trying to get a function to return a date based on the string in the preceding cell. If the cell, say A1, has the string "Before 12:30", i want the cell B1 to put the date 5 days from now in the cell. If there is any other string entered then 6 days from now. I also need it to include the networkdays function.
Here's what i've got, messy i know but functions aren't my strong point. Also would i save file size if i did this in VBA? ....
View 9 Replies
View Related
Jan 25, 2010
Ok so let me set this up. I have 2 columns: .....
View 14 Replies
View Related
Dec 2, 2008
using various keywords, and I haven't been able to find a solution that works. It would seem SUMPRODUCT is what I need to use, but thru all the combinations I've tried, I still can't get the syntax right. The problem is: I have dates in col A, and the word "Holiday" in col D. I need to create a formula that counts the number of instances of the word "Holiday" in col D, only where the date in col A is =DATE(2008,1,1))*(A11:A45
View 9 Replies
View Related
May 7, 2008
I have a sheet of data, and i need to extract rows that contains dates containing 5 in them. Eg, 5 Jan, 15 Jan, 25 Jan... 5 May, 15 May, 25 May and copy them into a new sheet. Is there a faster way to do it via VBA rather than manually extracting them?
View 5 Replies
View Related
Jul 25, 2014
I am looking for a formula that returns the latest Sale date for a each model of car. Below is sample data which I am trying to use the formula. I tried with below formula, but not successful.
=MAX(INDEX($A$1:$C$40,MATCH(A2,$A$1:$C$40,0),3),1)
MakeModelSale Date
AudiA4 11-Jan-14
AudiA4 quattro 12-Jan-14
AudiA4 quattro3-Jan-14
AudiA5 Cabriolet 14-Jan-14
AudiA5 Cabriolet q15-Jan-14
AudiA4 16-Jan-14
AudiA4 quattro17-Jan-14
AudiA4 quattro18-Jan-14
AudiA6 quattro19-Jan-14
AudiA4 10-Jan-14
AudiA4 quattro09-Jan-14
AudiA4 quattro10-Jan-14
AudiA4 11-Jan-14
AudiA4 quattro11-Jan-14
AudiA4 quattro11-Jan-14
AudiA8L 11-Jan-14
AudiA8L 11-Jan-14
View 3 Replies
View Related
Jan 1, 2014
I would like to ask how can I get to work the index match function (if there's any formula other than this current function its fine) if i have a start date and end date as range date then another criteria for name to get my desired result. im having a hard time to explain so i attached a workbook so you can around it.
I found a formula and tweaked it but still cant get to work.
View 10 Replies
View Related
May 21, 2006
ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.
View 9 Replies
View Related
Jul 7, 2006
I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non- zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference
View 6 Replies
View Related
Jan 22, 2008
I have 2 ranges: 1."Task info" (4x4 range)
2." Dates" (24x2 range)
I have one variable cell ref: "Date1" (a date)
Based on "Date1" I want to copy the value in the "task info" and Paste special value that is next to the corresponding date in "dates".
View 4 Replies
View Related
Feb 28, 2008
I'm trying to do a sumif based a range of dates but keep getting a "-" for the result.
In the past when using dates I've had to value the dates in order for it to work. I can't seem to figure out the formula this time around - any thoughts? See attached for sample - my 'broken' formula is in cell C29.
View 4 Replies
View Related
Jan 23, 2014
I have a database with a list of transactions with multiple fields, including:
Customer ID
Store ID
Item ID
Date
Price
I am wanting to add a calculated field that displays the first (or MIN) Date from the date column, looking at all of the orders where the customer ID, store ID, item ID and price are the same.
In the attached file I have shown what I currently have, as well as a solution using a helper column, and formulas I would use if I wasn't using powerpivot.
View 2 Replies
View Related
Dec 27, 2013
i am trying to project future date by adding certain number of days, the problem is that i m trying to catch starting date(C2) based on user defined(B2) date criteria is
a. if B2 is normal working day then C2 is the next day i.e. B2 + 1 for example if user enters 1 jan 14(B2) then C2 should be 2 Jan 14.
b. if B2 is saturday then C2 should be monday i.e. C2 + 2 eg B2 = 4 jan 14 then C2 = 6 jan 14.
now the main problem part
c. if B2 is saturday and monday is holiday then C2 should be date corresponding to Tuesday or if tuesday is also a holiday then C2 should be Wednesday and like wise..
d. same for last day of leave is to be calculated similarly to starting date..
I tried lots of IF combinations but it's not working...
View 6 Replies
View Related
Feb 24, 2014
I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?
View 2 Replies
View Related
Nov 1, 2011
I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.
Basically I have a sheet (called Sheet1) with the following columns:
A - Date (by individual day DD/MM/YYYY)
B - A catagory (so using the old example, Apples, Orange, Pears)
C - Price.
I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).
I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.
View 1 Replies
View Related
Apr 29, 2007
I would like to create a formula that compares a number in one cell to the dates I have displayed in two different cells. If true, then I want it to display the Currency value from another cell.
______A________B________C________D_____
|1| Due Date | Amount | 1/01/2007 | 1/15/2007
|2|___4________$98_______________________
A2 is the DAY it's due
B2 is the AMT that is due
I can create an IF...THEN statement where if A2 is >= C1 BUT less than D1 then the value is B2, BUT it only works if I change A2 to an actual date which means i have to change it every month...which would not help with forecasting future transactions. So in the example above, the value would be in C2 and not D2.
View 9 Replies
View Related
Feb 22, 2008
I have looked at the below Hide/Show Pivot Table Field Items help web pages:
Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria
I am trying to use the above, but with dates in the following format in each cell:
YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc
The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.
Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
View 4 Replies
View Related
Jan 21, 2010
I'm trying to do a Dcount (=DCOUNT($A$1:$M$229,Q$2,P14:V15))is there a way to do it without have to repeat the Criteria over and over?
Data Sample:StatePkg MeatCustomer #CT1MAMENHNJNYRIVT
Criteria Sample:StatePkg MeatCTHaveStatePkg MeatMAHave
View 10 Replies
View Related
Apr 21, 2006
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!!
View 7 Replies
View Related
Mar 11, 2013
I am trying to count unique records only in a column, if another criteria in another column is met. I got the Dcount to work by iteself, but I need to drive down the results more.
This is what I have:
=IF($G$2:$G$300="Q 000",DCOUNTA($D$2:$D$300,1,$K$2:$K$3),0)
The result I get is zero. In $K$3 I have =COUNTIF($D$2:D2,D2)=1).
View 2 Replies
View Related
