2-dimensional Date Range Criteria

May 22, 2007

Attached is a really simplified version of what I'm trying to accomplish. I'll attempt to get my brain working well enough to explain it:

For each row, I have a person's name, their department, their site, and then a series of dates (representing dates of attendence). The four columns after the dates ("Counts" is the first) are me trying to keep all the right numbers in mind, and are the routes I've been trying to take on this. I have also tried to make a custom formula called ClassInRange, which isn't playing nicely.

What I eventually need to populate is the little 2x3 section at the bottom where the three sites and the two departments I'm working with (down from 5 sites and 8 departments for the sake of sanity). What I need to be able to do is populate those cells with something that will tell me how many people from which site and department have attended something within the date range.

So, for example, I need to know that the 2 guys in Wellesley attended something between January 1st and December 31st of 2007. (American date style, in case anyone's lost)

If you're able to help, please keep in mind that I'm not the end-user; I'm just the slightly crazy dude trying to create this thing and make it simple to use. Eventually, the Dept/Site box will be part of a template which is copied and pasted up to four times on a report sheet, each fed by different date-range criteria.

Also, the important information is the person's name, dept and site, which I hope gives some reason behind the organization; we want to find the person easily.

View 9 Replies


ADVERTISEMENT

2-dimensional Date Range With Dynamic Copy And Paste

May 23, 2007

Some of you may have seen my 2-dimensional Date Range Criteria thread . By request, that thread has been closed and I am opening a new one on a related, but infinitely more complex note.

The solution to the previous thread worked, but not well with my project. I'll go a little more in depth into how my project works, and try not to be too detailed and irritating.

I am trying to create a homebrew Learning Management System for some of the Training courses that my department offers. I've posted here a few times on the same project, and the advice from all previous posts has been excellent.

What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question.

The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the 2-dimensional Date Range Criteria of the report.

What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.

View 9 Replies View Related

Creating A 2-dimensional Array From A 1-dimensional List

Nov 27, 2008

I've been a lurker on this forum for a long while and it's always been able to provide me with lots of excel tips, and for that I am grateful! But this time I have a question that I can't find the answer to here, or anywhere else on the web after a few hours of looking. As a note, I'm not very experienced with Excel, probably somewhere between novice and intermediate.

What I need to create is a 2-dimensional array of data. The vertical (y-axis) are the tools, and the horizontal (x-axis) are the jobs. Where the y-axis and the x-axis intersect, will be the quantity of tools needed for that specific job. There are almost 1500 tools, and 100 jobs.

View 4 Replies View Related

Bi-dimensional Dynamic Range

Jun 13, 2006

I've tried the below formula to get a bi-dimensional dynamic range that is defined by the last no blank column and last no blank row, too.

=A1:INDEX(A1:D18,MAX(NOT(ISBLANK($A$1:$D$18))*ROW($A$1:$D$18)),MAX(NOT(ISBLANK($A$1:$D$18))*COLUMN($A$1:$D$18)))

Does any one have a different approach to get this without using the offset function?

View 7 Replies View Related

Distinct Values From 2-dimensional Range?

Sep 18, 2012

I'm a huge fan of the DistinctValues function published by C Pearson here: Distinct Values Function

Now I have the problem that I need to get all the distinct values from a 2-dimensional range.

View 1 Replies View Related

Converting Range Data To 1 Dimensional Array?

Sep 10, 2012

I had this code and it works like a charm, except, it crashed if the data gathered is just one element due to transpose ..

VB:
Function getAR(c1 As Range, c2 As Range, w As String) As Variant
Dim s As String
Dim arrTemp() As Variant
Dim arr() As Variant

[Code]......

View 5 Replies View Related

Fill Range From Multi-dimensional Array

Jul 20, 2007

I searched and found a few posts about transposing arrays into a range of cells, but none of them seemed to solve my problem. So, my problem is, I have a .Net assembly which provides various functions to allow Excel to access our Oracle DB stored procedures/tables, etc. This assembly is exposed via COM Interop. I call the GetSPINTypes() method, which returns me a list of type pairs (ID, Name), in a CSV string format.

I split the CSV into rows, and then put each row into a 2-dimensional array.
I then need to dump that array into one of my sheets in Excel, so I try to do the usual Range.Value = Array, but this sometimes tells me there is a type mismatch, and most times just doesn't fill the range. I've checked my arrays in the watch window, and they have definitely been filled in correctly, the values just don't appear when they are put into the sheet. See the code I'm using below:......

View 4 Replies View Related

Insert Text In First Empty Cell In 2 Dimensional Range

Nov 20, 2013

I'm looking for a quick and easy way to insert a value in the first empty cell in a multi-column, multi-row range using VBA. I suppose I could loop through the range, but the table could grow to immense proportions and I don't want to slow everything down.

For example, the new value should be placed in cell C4. It doesn't matter whether the range is looped through the rows or columns, either will work just fine.

A
B
C

1
5
65
56

2
32
12
89

[code]...

View 1 Replies View Related

Next Date Matching Criteria Within Date Range

Jan 7, 2010

I'm setting up a spreadsheet to manage recurring tasks that fall within a given date range.

I have a named variable (theDate) which pulls the list of matching tasks that fall on a single day, but some fall on multiple days, so I need to somehow extract the next valid date the task will come due.

I've been playing with formulas until my brain got scrambled. I suspect I may not have got enough sleep over Christmas and there's something staring me in the face I'll kick myself over.

Column Headings are:
Description (Col B), [various notes C:E], M, T, W, T, F, S, S, (Cols F:L, marked with x when relevant), StartDate (M), EndDate (N) and my nemesis, NextDate.

That formula should check that 'theDate' falls within the range, if so, lookup whether the appropriate weekday is null or not, and if it's not, then the current date comes back. If it is, then I need it to find the next weekday (by that I don't mean M-F, I mean any of the 7) when it will fall and return that date. I do have the weekday number in F2 if needed, and I'm using a weekday return type of 2 (Mon=1).

'theDate' will usually be in the future, but not always. It needs to not fall over if it's past.

I'd like to do this via formula ideally, since the SOE I'm on keeps losing my Analysis Toolpak, but if I have to code, I have to code.

View 3 Replies View Related

Using A Date Range As Criteria

Jan 6, 2009

I have a problem with writing a set of macros to automate a report.

I have been asked to automate a report that at present has the person to add data into column L which relates to the dates in Column M. the dates are in the full date and time format. eg 20/10/2008 9:07:18 AM.

For example, if the report is being run for December's Data, you would first of all need to delete any rows that contain any date after December 08, so any jobs that were raised this month, then in Column L he would add "Raised This Month" for any raised in December and "Previous Months" for any raised previous to December.

I have looked at a few diiferent ways to try this and none have even come close. It makes it harder that the month for the data will change every month.

So to sum it up I need to use the dates in Column M to affect the data in column L, also delete the row if column N is earlier than a certian date.

View 6 Replies View Related

Date Range With Other Criteria

Aug 25, 2008

I am trying to get a count of cells that fall within a specific date range. In my case, I need cells from today and minus 6 days from now. e.g. last Friday until this Thursday. I do this weekly.

I can get the correct count of total entries for the period with:

=COUNTIF(($D$2:$D$10324),">="&TODAY()-6)-COUNTIF(($D$2:$D$10324),">="&TODAY())

But, I need to add more criteria. I need to add type and status.

For example...I need the count for that period that is type "Email" (there are others..Fax, In Person..) and status "Closed" and/or "Resolved".

I had tried SUMPRODUCT, but can 't get it to work.

I was looking at a formula like this:

=SUMPRODUCT(COUNTIF($D$2:$D$10324,">="&TODAY()-6)-COUNTIF($D$2:$D$10324,">="&TODAY())*($B$2:$B$10324="Fax")*($C$2:$C$10303="Resolved")*($C$2:$C$10303="Closed")


Is SUMPRODUCT the way to go, as I can't get it to work at all.

View 9 Replies View Related

SUMIF But Where Criteria Is Date Range?

Apr 28, 2014

I am trying to get a SUMIF formula to work where the following: - RANGE = Column N:N (These are all a list of dates) CRITERIA = Cell C20:D20 (These are the dates i want to look at) SUM_RANGE = Column P:P

Basically, I want to SUM all the numbers on Column P where the date in Column N falls between and including the dates in Cell C20 and D20

In attachement, I want to show in the YELLOW cells the SUM of Column P where the Date in Column N is between the Dates in column C and D.

View 3 Replies View Related

Countif Between Date Range And Criteria Met?

Jul 23, 2014

I have a start date column and column for status. Status is either "in-progress" or "complete". I want to count the number of "complete" items that fall within a specific date range. I have tried countif but I can only seem to get a count of all items that fall between the date range. I do not want a pivot table for this.

View 3 Replies View Related

Count Date Range With Criteria?

Jul 24, 2014

I have a date range that I'm trying to get a total count on. The criteria is any date in COLUMN I that is not "approved" in COLUMN L and the date is less than the current date which I have in cell U5.

[Code] ......

View 12 Replies View Related

SUMIFS With Date Range As Criteria?

Dec 9, 2013

I have time-worked spreadsheet use to calculate the hours worked over a year, now I need to sum the hours worked during a given period if they happen to be on a public holiday. I have a column (say A) displaying dates from 1/1/13 till 31/12/13, another column giving the result of the worked hours (say B). I also have a defined list of public holidays which I need to compare with (named Holidays). The working year is "divided" in 13 periods of 28 days each (the last one having 29 days). I was trying to use the SUMIFS function (since i have other constrains that need to be met)
e.g. =SUMIFS(B1:B28,A1:A28,Holidays)

View 9 Replies View Related

Dificult Sum + Sum.if Date Range Criteria

Jun 19, 2009

0 x
-2 x
-1 x
3 x
-4 x
1 max
5 x
8 x
9 x
1 x

I need to sum the 1st column from the bottom, to the same line of the max on the right column.
First thing i remembered =sum(xx:max(xx:xx)) but it doesn't work, i think because sum() does't allow any operations in its arguments.
Second was to store the line of the max and try to join the column and then sum them but =lin(max(xx:xx)) doesn't work either
Third i tried =sum.if() but i don't have much experience with this function.

Also i'm trying to perfect a SUM.IF with a DATE RANGE CRITERIA
instead of:
=SUM.IF(I4:I25,">=2009/1/1",R4:R25)-SUM.IF(I4:I25,">=2010/1/1",R4:R25)

why can't i use?
=sum.if(xx:xx,year(xx:xx)=2009,yx:yx)
or
=sum.if(xx:xx,year(xx:xx)="2009",yx:yx)

View 9 Replies View Related

Median With Criteria - Price And Date Range

Jan 30, 2014

I am trying to find the median for a huge amount of data, so I have cut it down to just an example size. I have uploaded a copy of the example file.

I have three price ranges that I have to find the median for:

Under $5MM
$5 MM - $25 MM
$26 MM - $56 MM

I need to find the median for each price range for Signed units and Sold units. I also have to find a median within a date range (e.g. within the last six months) that includes the previous criteria.

For the first formula, I have tried using the following formulas:

=MEDIAN(IF($I$14:$I$34,"Signed",IF($F$14:$F$34,">="&0,IF($F$14:$F$34,"<="&5000000,$F$14:$F$34))))

=MEDIAN(IF($I$14:$I$34,"Signed",IF($F$14:$F$34,">=0",IF($F$14:$F$34,"<=5000000",$F$14:$F$34))))

(I remembered to do "CTL + SHIFT and ENTER" to make it an array.)

For the second formula, with the date range, I am able to find the total for the last six months, but not with a price range included.

The formula that I am using that works for the median of units Signed within the last six months is:

=MEDIAN(IF($H$14:$H$34="Signed",IF($G$14:$G$34>"07/31/2013"+0,$E$14:$E$34)))

However, once I try to find the price range, it does not work:

=MEDIAN(IF($H$14:$H$34="Signed",IF($G$14:$G$34>"07/31/2013"+0,$E$14:$EIF$34,IF($E$14:$E$34,">=0",IF($E$14:$E$34,"<=5000000",$E$14:$E$34)))))

View 2 Replies View Related

Sumproduct With Date Range And Multiple Criteria?

Aug 19, 2012

The part in green will count the number of entries for the name Johnson & Freedman LLC perfectly fine. However when i add the last part in red i receive a #Value! error.

Col. W is formatted as General and has a data validation for the user only to choose Pass or Fail.

Not sure why it isn't working.

Code:
=SUMPRODUCT(--( 'SCRA'!B26:B29>=Sheet3!C2),--('SCRA'!B26:B29

View 8 Replies View Related

SUMIF With Year As Criteria And Date As Range?

Apr 2, 2014

I am trying to do a sumif off all dates that fall into a specific year. I know I can do it by either adding a column in either of the sources to get the translated data but I was wondering if I can get this to work by it recognizing the format within the formula only.

View 4 Replies View Related

IF Then, Two Criteria, Date/Time Range Formula

Jun 14, 2007

I am having a hard time with this formula. How can I adjust my formula so that everything that falls outside the boundries of my given date range will have the word "DELETE" next to it? Here is what I have and it is not working....

View 9 Replies View Related

Lookup 2 Different Criteria Then Average Between A Date Range

Jan 6, 2009

For those of you that love difficult ones, here's one..

I have a sheet that has a column of names.. Across the top row I have dates.. let's say from the 1st through the 31st.... in the body of the report i have different sales numbers and what I am trying to is the following...

1/1 1/2 1/3 1/4 1/5
Johnny 3 4 2 2 1
Becca 3 4 1 1 1

So let's say in this instance, mind you my real report is about 342 rows of names and stats.. Is look for Johnny between 1/3 and 1/4 and average those numbers between those 2 dates...

I've tried everything from sumproduct to vlookups and can't seem to get it right...

View 9 Replies View Related

Count Between Date Range & Text Criteria

Aug 17, 2008

I have an excel file with 1000 of records for eg.

Date INITIALS CATEGORYACATEGORYB
31-Dec-07MTSVIN
01-Jan-08MSSVDI
04-Jan-08MORSKH
31-Jan-08MPSVHI
02-Feb-08MPRSIN
03-Feb-08MPVSIN
02-Jan-08MSRSIN
03-Mar-08MSKSKI


i want to count the datas

1) SELECTING IN THE SPECIFIED DATE RANGE (e.g from 1st jan to 31st Jan)
2) SELECTING MULTIPLE CRITERIA IN B COLUMN (MP & MS)
3) SELECTING MULTIPLE CRITERIA IN C COLUMN (SV & RS)
4) SELECTING MULTIPLE CRITERIA IN D COLUMN (IN & HI)

My colleagues tried countifs in excel 2007 but i have 2003. i want to specify all the four criteria in a single function to achieve my result (IN EACH MULTIPLE CRITERIA)

View 6 Replies View Related

Multiple Criteria Lookup With Date Range Match?

Dec 30, 2008

I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant.

View 9 Replies View Related

Multiple Criteria Lookup With Date Range Match

Oct 12, 2012

I'm trying to create a formula that will lookup multiple criteria within a table, however the problem I am having is that one of the criteria needs to fall within a certain a date range. I've used index & match to look up the dates when they match exactly, but how to return the same value for when they fall within a date.

I have attached the spreadsheet - Date Range.xlsx! The requirement is to get the correct "pay" from the "Contingent Workers" sheet to the Timesheet sheet. So it will match the Employee ID, but also the date on the Timesheet tab will fall between/or be the exact date indicated on the contingent workers tab.

View 3 Replies View Related

Index Match Based On Date Range And Name Criteria?

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

Index With Multiple Criteria Including Date Range

Dec 2, 2013

I know need to modify the formula to include validating if the indexed reference occurs within a certain month.

{=INDEX('Duration Calculations'!B$2:B$5000,SMALL(IF(MONTH('Duration Calculations'!$J$2:$J$5000=10),IF(ISNUMBER(SEARCH("br",'Duration Calculations'!$A$2:$A$5000)),ROW('Duration Calculations'!$J$2:$J$5000)-ROW('Duration Calculations'!$J$2)+1)),ROWS(P$100:P101)))}

Essentially, what I am trying to do is index the value from B column if it meets the following criteria

1) Date in column J is equal to any date that occurs in October
2) the Column A has a text value equal to "br"

If both true then I need to index the value in column B.

The formula as states works finding any value equal to "br" but seems to be ignoring the date range.

View 1 Replies View Related

Sumifs With Date Range Criteria By Referring To Ribbons

Apr 14, 2014

I'm trying to create a sumifs that has 'before date X' and 'on or after date Y' as two of the criteria.

In the past, I have simple done a DATE(x,y,z) function inside the sumifs, but I'm trying to change this for reasons that would take a little while to get into. Let's just say it would make my coworkers lives much easier.

My hope is to get these date range criteria by referring to ribbons which have the dates in mind in them. Here is the formula I have now, and an example. See the red part of the formula.

Say I'm trying to have before Aug 1, 14 and on/after July 1, 14 as two criteria. FBP column A is where I would have the dates that this criteria would search through:

B1: 7/1/14
C1: 8/1/14
Formula: =SUMIFS(FBP!$G:$G,FBP!$B:$B,"=E",FBP!$A:$A,">="&B1,FBP!$A:$A,"

View 5 Replies View Related

Microsoft Query :: Put Floating Date Range In Criteria

Jun 21, 2007

Is there a way to put a floating date range in the criteria much the same as can be done in excel ie greater than Today() but less than Today()+3? I don't want to keep going in and adjusting the query if I don't have to and if I don't keep the range tight there is too much data.

View 9 Replies View Related

Multiple Criteria Lookup With Date Range Match

Dec 30, 2008

I am trying to create a formula that will lookup multiple criteria within a table, one of those criteria being a date that falls within a certain date range. The purpose of the data is for billing tenants and owners in a building. I have successfully been able to use Index Match to lookup 2 criteria, one of them being a specific date match (see my attachment). However, I need to add a third criteria looks up the date that falls between a specific date range. The concept is to run a query where I enter a Unit (apartment) number into one cell, a Billing Date into another cell and the Type (Owner or Tenant) into a third cell to determine the Name of the person who fits these criteria... Criteria A Lives in Apt XX, Criteria B Who's lease start date and end date is inclusive of the Billing Date that I entered and Criteria C who matches the Type of customer, either Owner or Tenant. I have attached my sample spreadsheet.

View 7 Replies View Related

Copy Range & Paste Based On Date Criteria

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







Copyrights 2005-15 www.BigResource.com, All rights reserved