Array Formula: List The Number Of A Specific Class For A Particular Month (any Day)
Dec 30, 2009
I'm having difficulty creating an array formula. In a multi-column sheet, I am looking at a column with classes and a column with a date (in the format 7-Oct-09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes:
=COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and
=SUM((A4:A2500="AA")*(H4:H2500="??-Oct-??")) which gives me 0. Maybe an array formula is not the way to do this.
View 9 Replies
ADVERTISEMENT
Jul 31, 2009
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.
Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009
I'm using Excel 2007.
View 9 Replies
View Related
Jan 18, 2012
I'm creating a simple bit of code which needs to include an array formula - but the code below fails;
Selection.FormulaArray = _
"=IF($L3="","",IF(ISERROR(INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1)),0))),0,INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1))))))"
"Runtime error 1004: Unable to set the FormulaArray property of the range class"
What am I doing wrong? The formula is fine when typed in.
View 7 Replies
View Related
Mar 3, 2014
I'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. I still get the same error.
[Code] ............
View 1 Replies
View Related
May 16, 2014
if there was a way to automatically add each student to a class list as their information is inputted. What i mean is, after input Sally Student in the main enrollment list she would automatically go to the class list for the class she has chosen.
I have attached a sample workbook with my desired results. Basically what i want to do is make this easier for those in charge to get the class lists from the main list without having to copy and past all the time.
The class lists are in the tabs in the workbook.
View 5 Replies
View Related
Aug 29, 2013
At the end of each month, my agency collects data about clients served in our programs. One of the many pieces of information they want is the number of "bed days" a client was residing within our programs for the given month.
For example:
Jon Doe entered the program on 7/16/13 and discharged on 8/15/13. I would like to know how to calculate the number of days in July that Mr. Doe was residing in the program. (The date of discharge is not included.)
I recently took over the responsibility of completing reports such as this one at my office. The previous person who completed the monthly reports would count the days by hand for each client, but I know there must be a better way.
View 6 Replies
View Related
Aug 22, 2014
I am trying to create a graph that is conditional on two different columns. The first column is a date column, the second column has various categories. I want to show how many times each category appears per month. This database is continually added to so I wanted the formula to reflect the entire column range.
For example, let say I have 5 categories (Grapes, Apples, Peach, Pear, Banana). Column A would show a date (in a M/D/Y format) and Column B would list the fruit type. I want to show how many Grapes were input in January, February, March, etc. and then move on to show how many apples in each month, and so on.
View 5 Replies
View Related
Mar 6, 2009
I am trying to write an array formula that will work out how many minutes of tickets are open within a particular month. I have a formula shown on the first table which calculates how many minutes a ticket is open for within a particular month, but this only works for an individual ticket, Basically I neet to populate the 2nd table with an array formula that will calculate the number of minutes within the month that numerous tickets are open, effectively a SUMPRODUCT but I can't get it to work.
The right hand side of Table one doesn't exist in my main file, I am trying to populate the 2nd table using only the first four columns within Table 1.
******** ******************** ************************************************************************>Microsoft Excel - My God.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF3G3H3F4G4H4F5G5H5F6G6H6F7G7H7F8G8H8F9G9H9F10G10H10F11G11H11F12G12H12F13G13H13F14G14H14F15G15H15F16G16H16=ABCDEFGH1 01/02/2008 Jan 08Feb 08Mar 082 3129313W00000220/01/2008 17:33:2629/02/2008 02:36:5456037 16,035.8540,001.15-4W00000127/01/2008 01:05:4620/02/2008 03:17:52200 41.13158.87-5W00000122/03/2008 09:06:1824/03/2008 08:33:57789 --..............................
View 9 Replies
View Related
Jun 14, 2008
I looking for a macro which will help to open a file with current week number in name.
The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009.
I've made two dimensional array (week number, weekday) with all the dates from that period.
I have problem with code to search through the array for given key, return index and write it into variables.
For now my code looks like:
Sub week()
Dim i As Long
Dim j As Long
Dim k As Long
Dim week(51, 6) As String
View 5 Replies
View Related
Mar 14, 2014
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
View 3 Replies
View Related
Jan 29, 2013
My problem is , I have a date range 21-Feb-2013 till 07-Mar-2013 (Col C2, Col D2 respectively) which is holiday list for a person.
I am trying to pull out the number of working days for that person in the month of Feb which should exclude weekends.
To get the total no. of working days for the month of Feb, I have used the below formula.
=NETWORKDAYS(DATE(YEAR(C2),MONTH(C2),1),EOMONTH(C2,0))
This gives me a value of 20 which is correct.
Now I need to find no. of days which fall under the month of FEB from the date range 21-Feb-2013 till 07-Mar-2013 (Col C2, Col D2) which are working days. so that I can subtract that from no. of working days for that month (FEB) to get the no. of working days which the person has actually worked.
View 3 Replies
View Related
Jul 6, 2007
is there a formula to list the first and the last day of each month and for every year?.
example:
if i put in cell A1: 01/09/06 ----->01/September/2006
the result should be:
A2: 30/09/06 ------> 30/September/2006
A3: 01/10/06
A4: 31/10/06
A5: 01/11/06
A6: 30/11/06
A7: 01/12/06
A8: 31/12/06
A9: 01/01/07
A10: 31/01/07
A11: 01/02/07
A12: 28/02/07
.
.
.
and so on
how to do that with a formula?
View 9 Replies
View Related
Nov 29, 2007
I have a class
Private MemoryArray() As Variant
Private Sub Class_Initialize()
Redim MemoryArray(0) As Variant
End Sub
Public Sub ReplaceMemory(GivenArray() As Variant)
Redim MemoryArray(UBound(GivenArray)) As Variant
For Index = LBound(GivenArray) To UBound(GivenArray)
MemoryArray(Index) = GivenArray(Index)
Next Index
End Sub
that I am specificall passing an array to the replacememory sub. So in the program I have a global array doved criteria:
Dim Criteria() As String
Sub Product2()
'fill criteria with various entries
'do whatever in macro sub
Set MemoryCriteria = New Memory
MemoryCriteria.ReplaceMemory (Criteria)
and on the last line I get an error message:
Compile Error:
Type mismatch: array or user-defined type expected
View 9 Replies
View Related
Nov 25, 2009
Its my first post in this forum. Hope you would provided me proper guide line to solve my problem
Suppose I have a column of numbers. such as
01010001
01101010
01201211
01212122
02101201
00102101
00010101
01010001
.
.
.
I want to find the possible set (groups from these number) which adds up to a constant let 03333333
let for example
if I add
01010101
01211201
01112031
Now adding these numbers results in 03333333
whatever the possible groups, should be listed.
View 9 Replies
View Related
Jun 16, 2008
I have checked searching the forums for this but nothing that really matches what I am looking for. I am trying to, from a date, deduce how many days there are in that month. I have tried a solution where I look at EOMONTH adding and subtracting but this is quite cumbersome. Is there some code out there where someone has solved this?
View 9 Replies
View Related
Dec 23, 2008
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
View 4 Replies
View Related
Dec 1, 2012
In a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
View 3 Replies
View Related
Jan 11, 2007
I have 2 columns in a a sheet, some cells in the second (not all cells) contains the current month's number (it comes after the multiplication sign in those formulas).
i need a macro that allows me automatically to advance the month's number each month in this formula (just the month's number), if it's 1 advance to 2, 2 to 3, 12 to 1 etc....
see a example of my formulas below, the formulas are in colomn B, the month number is whatever comes after * (multiplication sign).
example:
5500/11*12
12000*12+55000
=A2/11*12+55000
(i'm using excel 97).
View 14 Replies
View Related
May 7, 2014
In cell A1 = 15/06/2006 (dd/mm/yyyy)
What is the formula to find how many no. of days in month provided in cell A1.
View 2 Replies
View Related
May 25, 2012
I have created an analysis worksheet but need to a formula that looks up against a list, hence why I think (and hope) that this might just need an array formula.
So the worksheet has a few thousand rows of data (one for each order) that is put against an email address. I need the formula to sum the revenue generated by 'club members' who placed on a specific date whos email appears in a list on a seperate tab. If it wasnt for the email list I would use a simple sumifs formula but I am stumped by the need to look up against a list of emails.
As close as I've got is the following;
=sumifs(data!AC:AC,data!Z:Z,"Club",data!L:L,B11,data!S:S,'Discount'!B:B)
the data tab is the one with all the data and the discount tab contains the list of email addresses to look against (B:B). AC is the revenue data, Z is whether they are club members, L is the data (B11 is the date to look up against).
View 9 Replies
View Related
Apr 10, 2010
I'm looking for array formula that will condense a long list like this:
a
a
a
b
c
b
d
To this
a
b
c
d
View 5 Replies
View Related
Jul 26, 2013
I manage a list of web-based classes offered by my company - each class name, date, time, and attendance is recorded in the list. Periodically, we send out marketing emails for these classes to promote attendance, which is also recorded in the list.
Issue: I have a summary list of the class names (where each class is only listed once) and I'm trying to come up with a formula that will pull the class date for the last time that course was marketed.
I have attached an example of what I am trying to do - using colors instead of class names. The last date marketed for each color is already hard-keyed into the summary to show what should be there.
View 4 Replies
View Related
Jan 23, 2012
I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. Any way to identify the number of days, per calender month, that falls in a date range.
sample data...
Start Date
End Date
Old Value
New Value
08/03/2010
18/06/2010
16758.2
16758.1
[Code] .......
I need to break down the total number of days per month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
24
30
31
18
[Code] ......
As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...
I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.
(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it).
View 8 Replies
View Related
May 15, 2014
I have a sheet of data that I can filter (e.g. only show male pupils).
On the next sheet I want to display the list of filtered results with no gaps.
As the user will be able to select which column of data to show from the first sheet I am referencing it as follows:
INDIRECT("'Progress Matrix'!"&ADDRESS(MATCH($B$6, 'Progress Matrix'!$A:$A, 0)+1,
MATCH(C$6, 'Progress Matrix'!$1:$1, 0))&":"&ADDRESS(300, MATCH(C$6, 'Progress Matrix'!$1:$1, 0))))
(However this formula no longer seems to work as I think I changed something).
I've tried to incorporate
SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))
to only show filtered results but with no success.
Progress Tracker.xlsm
View 6 Replies
View Related
Nov 30, 2007
So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}
But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}
SUM by both a specific month, and a specific year from a single date field?
View 3 Replies
View Related
Apr 27, 2014
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies
View Related
Oct 3, 2012
I have this array formula
=IF(ROWS(A$11:A13)1,IF(MSB!$A13"",SMALL(ROW(Table1[APP DATE])-ROW(MSB!$A$11)+1,ROWS(A$11:A13)),""))))
the problem is that when both if statements are true i want it to get the row number, this is because table1 has blank rows (can not be avoided as the data is linked from a closed workbook)
i can see this works up until it meets the first blank row and i get #value error.
on table1 data exists on row 1,2,5 and 8 so my problem is getting the small function k to report these numbers?
View 6 Replies
View Related
May 30, 2014
I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:
List1
List2
Final List
Audi
Ford
[Code] ....
So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.
{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}
View 2 Replies
View Related
May 15, 2009
I got the following loop that is very slow. The main way (I think) to speed it up is to avoid looking at all the 65000 cells. The columns used in the calculations have data varying from a few hundred cells up to (rarely) dozens of thousands. I assume it would be better if I could replace the 65500 with something indicating the "end" of the data in the column. Also, is there another way to do this process without using a loop?
I am trying to fill in a number of rows with array formula's in the adjucent cells. I use the loop because the number of rows depends on a selection of another parameter which sets the row number before calling the loop.
View 5 Replies
View Related
Oct 24, 2013
I am looking for a formula which will allow me to return a value, 1 or True, if any of a number of words or phrases in a list exists in a specific cell. Per the below example, any formula which will achieve the result in column A
For Example:
Sheet 1
Column A
Apples
Oranges
Banana
Fruit Salad
Fruit Smoothie
[code].....
View 6 Replies
View Related
