I only want to include the Month of January in a sumproduct function on another page using a name manager array.
I was thinking of using
=SUMPRODUCT(INDEX(DATA,0,17),--((MONTH(INDEX(DATA,0,5)=1)*INDEX(DATA,0,17))
Where the dates are in the 6th column and include blank cells at the bottom and in the first row it says Date.
In the following formula I'm looking for MONTH 1 (January) and WEEKDAY 1 (Sunday):
=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$6936)=1),--(WEEKDAY(Sheet1!$A$2:$A$6936)=1),--(Sheet1!$D$2:$D$6936=$D2),Sheet1!E$2:E$6936)
is it possible to find MONTH 1 and 2 (Janauary and February) and use the rest of the formula as is? Can the same thing be done looking for MONTHs 3,4,5 or months 8,9,10,and 11?
I have a problem calculating something that happened last month if the month is january. At the moment, if the month is not January it works:
View 3 Replies View RelatedI'm graphing data from November 1994 to present. Since it's such a long time period, I only want to indicate a given year once (1994, 1995, 1996, etc.) with a tick mark every quarter (e.g. 1994 | | | 1995 | | | , etc).
I'm having difficulty doing so since the graph starts in November. The graph right now shows 1994 as a full year and thus the data doesn't correspond with the correct dates.
I have about 80,000 dates. What I need to do is to place an x by the date if it falls inbetween January. For example, I have start Date in A1 and End date in B1. If the start date was 10/20/2013 and the end date was 6/11/2014 then that woub give me an "x" because it went through January. If the start date was 11/23/2013 and the end date was 1/1/2014 that would give me an "x" because it fell in January but if I had 8/4/2013 as a start date and 12/29/2013 then that would be blank. Hope that I made myself clear enough.
View 2 Replies View RelatedHow I can subtract a year when I formulate based on if the current month is January.
Such as Current month = January 2014, I would require the cell to populate December 2013, any other month would return the current year value.
I have currently tried { =DATE(YEAR(A3)-1,MONTH(A3),DAY(A3)) } whereas cell A3 uses NOW() function formatted to MMMM, but it changes for every month.
I'm using a formula (with the SUMPRODUCT function) within which I need to match the month & year from an array with 3 "true" possibilities...
Meaning that if data (month & year) from the array matches any of the 3 months & year of a specific quarter (for example July 08, August 08, September 08), the result of that section should be "True".
The data to be matched (any of the 3 months of each quarter) is drawn from a table header (showing every month of the year) by using this "monthly" formula:
TEXT(H7;"mm.yyyy") where H7 holds "01.07.2008" (but displays "Jul. 08") and returns "07.2008".
I'm trying to extend it to something like:
TEXT({H$7;I$7;J$7};"mm.yyyy") to match either July 2008 (H7), August 2008 (I7) or September 2008 (J7) for the 3rd quarter of 2008 but when I hit Enter, I get an error and H$7 is highlighted ("ctrl + shift + enter" doesn't help).
Here's an excerpt of the formula that works well for other queries on my worksheet:
=SUMPRODUCT(--(TEXT('Current update analysis'!$O$2:OFFSET('Current update analysis'!$O$2;(COUNTA('Current update analysis'!$B:$B)+20);"mm.yyyy")=TEXT({H$7;I$7;J$7};"mm.yyyy")); --('Current update analysis'!$k$2:OFFSET('Current update analysis'!$k$2;(COUNTA('Current update analysis'!$B:$B)+20)="ok"))
how I could/should structure the formula for it to work?
could provide me a loop function in VBA for these calcultions.
Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1
These are the calculations for E1-G1.
The process must be repeated till E50-G50
So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2
With the formula I have as:
=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01)
I want to dis-include the CC="06" in the summation. I tried using:
=SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)
But it gives a wrong calculation
I use "--" because i have character in the cell
I have a criteria issue with a sumproduct formula i'm using. The criteria is essentially supposed to say "if the value in column I (in Sheet2) equals ANY value in column O (in Sheet1), then do not include that line in the SUM. Here is the complete formula:
[Code] ............
I'm looking at the formula in Sheet3, cell F5. You'll see that the criteria "--(Sheet2!I:I<>Sheet1!O:O)" does not seem to be working. I need the formula to not include any values from Sheet2!E:E, where the corresponding value in column I does not equal any value in Sheet1!O:O. Also, these values can change by user so its not as simple as just typing them in to the formula as an array...
That VBA part of this is that it's in a macro, so if there is any way to make this easier using code instead of a simple formula.
I'm using Excel 2007 and have an Employee Scheduling Program that keeps track of 10 employees on a monthly basis (1 worksheet per month). The days of each month are in columns (I thru AM) and my 10 employees are in Rows 6 thru 15, which creates a grid of cells. I use Conditional Formatting to highlight the Weekends, Todays Date, and Holidays. My Sumproduct formula (shown below) is in each of the cells of my grid and places a number (1 to 10 for each employee) from start date to the end date. My Current formula works great as it finds every occurrence of the argument but I need to modify it to include the contents of the Helper Column.
If(Sumproduct(($g$44:$g$74=$c$6)*($m$44:$m$74<=i$4)*($t$44:$t$74>=i$4)),1,0).......
I have no problems figuring out SUMIF or SUMPRODUCT with multiple criteria, but I'm trying to help someone with an issue that the second criteria is actually not all-inclusive.
Column B - Lots of numbers, the SUM range
Column C - Yes and No flags. Y or N
C1 = maximum number of matching items to include.
So, =SUMPRODUCT((C2:C100="Y")*(B2:B100)) sums up ALL the Yes rows, but the guy wants to restrict it to the last X matches, and put that X factor in C1. So if C1=5, only the last 5 Y matches are included.
I am trying to update a spreadsheet for 2012 to 2013 and want to use a formula to change SUNDAY 8 January to Sunday 9 January and so on for rest of year.
View 1 Replies View RelatedI have a a set of date ranges. The start and stop date of the ranges are listed in seperate columns. I need to count how many of those date ranges include a specific month/year. Example data is below.
Start
Stop
Month/Year
[Code].....
I am having a ton of trouble with this. I am trying to add a single text row in between existing data. I have tried multiple loops to try to place an array into Column G, but I cannot figure it out. Is there some coding that will produce the sort of result below in column G?
View 6 Replies View RelatedI m trying to use arrays in the wrong way. I simply want to create an array from values in a column and perform calculations on each element and store the results for each element. I therefor need a 2d array. I also dont know the number of values in the column and therefor dont know the number of elements that will be in the array. How would I declare this array. Ive tried Dim MyArr(1 To ActiveSheet.UsedRange.Rows.Count, 1 To 2), but that returns an error - 'Constant expression required'.
View 6 Replies View RelatedBasically, I'm trying to alter the code by inserting another condition where if S12 = R than it will exclude the value found in D12, other wise D12 is included. It was initally setup with a range of $C12:$H12 but i had to write it out long to exclude $D12
Here's my ammended ...
A few weeks back you guys were kind enough to help me figure out a good way to use SUMPRODUCT to fetch a value from a third column using values from, in my case, two other columns. It has been a lifesaver! Now that my database grows to more than one sheet however, I am again stumped finding a method of using SUMPRODUCT to search the same data base on the next page of the workbook. Everything is the same except value of the numbers so if I can figure out how to make SUMPRODUCT drop through the first empty cell or something similar, I can continue my database search without more sleepless nights.
The values are in few enough columns such that one could put them all on one sheet, however I don`t know how to make Excel start at the top of a particular column once it has been unsuccessful in the first attempt to find the value.
If have very simple =IF function, =IF(F4=H4;1;0) but don't wan't the 'value is true' value when both cells are empty, how do I need to change the function so it states 0?
View 3 Replies View RelatedI have a database that shows repeated templates down the page...ex.
"Site" (here) Remnant
Jan
Feb
Mar
"Site"(here) Remnant
Jan
Feb
Mar
All after another using the same template.
I am trying to sum all the occurances that fit the criteria Jan and Remnant:
I have this:
=SUMPRODUCT(--('"STATIONS"'!$A$4:$A$300='"GROUP"'!$A3)*('"STATIONS"'!C$3:R$3='"GROUP"'!C$2),'"STATIONS"'!$A$4:R$300)
But I get a N/A , my reference A3 matches the dates in the database and C2 matches the repeated occurances of Remnant...
Is this possible since my templates keep repeating down my database?
Basically, each template in the database is related to different stations, and I am trying to sum all the stations details in the database for each month and Header"Remant"
I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.
I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.
VB:
Dim Cell As Range
' 1st cell with the posting date
Set Cell = Range("A2")
Do While Not IsEmpty(Cell)
If Cell = "Date" Then
[Code] .....
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
I have never worked on workdays before so I do not know most of the available formula and play around. I googled across and found this one: [URL] .......
Where Bob has given an ARRAY formula.
Following is my layout in A1 to C3 grid (for testing purpose):
Start Date11/1/2011HOLIDAYEnd Date11/30/201111/3/2011NETWORKDAYS2011/22/2011
The formula resides in B3.
Is there any other development in 2007+ versions of Excel which can handle 6 working days in a week?
I have 3 or 4 criteria and one data range to sum based on these criteria. If I remove date as a criteria, I can make a sumproduct formula work, and even if I include a specific month it works as well, I would like to write a formula that uses the most recent month with data as a criteria. My reasons are that some banks pay our reps monthly, some quarterly, some semi-annually, and some whenever they damn feel like it. I need to take a snapshot of what each rep has invested in each bank: So for Bank A his last Statement came in March, but Bank B sent a statement in January. Here's what I have so far:
=SUMPRODUCT(Month('2007 Data'!$F4:F$97),'2007 Data'!$G$4:$G$97=Sheet1!$A2,'2007 Data'!$B$4:$B$97=Sheet1!$G$1,'2007 Data'!$H$4:$H$97)/(month('2007 Data'!$F$4:$F$97)
I know that the Month function here is incorrect but I'm going bonkers trying to wrap my head around this.
FYI, 2007 Data is my Data page, Col F = Date Range, Col G = the Rep ID, Col B, = the Bank name, Col H = Assets Invested; Sheet1 = my results page.
I know, I KNOW, many of you will scratch your heads and say this idiot should just use a Pivot table, I know. I have. Let's just say some of the end-users are not comfortable with Pivot Tables. I've come up with a simple listbox/command button to populate the axes of the chart (Column A = Reps, Row 1 = Banks)and I need this formula inside the chart.
After seeing and learning from many of the posts on this site, I'm sure someone knows how to acheive what I'm after, I think it's just a matter of my communicating it as well as I can.
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
View 10 Replies View Relatedfunction in a spreadsheet that will list all of the days in
a given month automaticaly with the entry of the 1st of the month only.
Ex;
10/01/05 entered dated
10/02/05 auto fill
10/03/05 "
. "
. "
10/31/05 end of auto fill
I would like the function to stop filling dates at end of the month even for shorted months such as Feb.
There is a date field and three "attempt" fields.
If The first attempt was a fail and the second a pass this must count as a pass and not a fail. If the first attempt was a fail and second was a fail then this must count as a fail, same again for third column.
On the totals tab there should be a total against each date for the fails. As you can see iv tried doing by sumproduct but its not working, i understand why its not working but i just can't see how I can get it right.
I am trying to count dates from a list using sum product (I found the formula via google) I have plugged it into my spreadsheet but it does not seem to be calculating correctly.
I have attached the spreadsheet too : sumproduct_Error.xlsx
=SUMPRODUCT(YEAR(Tbl_finish[Finish]=2008)*(MONTH(Tbl_finish[Finish]=1)))
I am expecting a count of all the dates in January 2008 with the formula above.
Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.
PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.