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 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.
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 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 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 RelatedI 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.
I have a list of items in column A. Column B has each item's net price. Columns C-N shows the consumption of the items per month. Column O shows in which location the items are stored.
I need to do a sumproduct so that it shows the value of the items retrieved from that particular location per month.
If it didn't have to be by location, I would've simply done something like =sumproduct($B1:$B10,C1:C10) and copied it across the columns. How do I tell excel to sum per location as well? I know there is a simple solution to this, just not seeing it...
EDIT: I tried =SUMPRODUCT(($B1:$B10)*(C1:C10)*(O1:O10=$B1)) but it gives a #VALUE error
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 ...
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies View RelatedI have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.
So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...
So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.
Attached File : Time and Attendance.xlsx‎
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] .....
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
View 4 Replies View RelatedIn 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.)?
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 log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
Jan-09
Feb-09
Mar-09
Apr-09........
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.
Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.
View 2 Replies View RelatedI am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...
January 1 2012 to January 31 2012
The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.
I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month.
Goal:
I need to format the spreadsheet to make it more readable and have been assigned the task of:
1 - Inserting a blank row between each row that contains data and filling in with color.
2 - Resizing the blank row to make it look like a "thick" border.
3 - Auto adjusting the columns to correct size.
4 - The last column contains comments and needs to be wrapped text.
5 - All of this needs to fit on 1 sheet (landscape).
Issues:
1 - Each month there will be a different number of rows.
2 - I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls?
I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.
My data sheet has a Month column, and those months are simply numbers one to twelve. They are formatted as plain old numbers. The data is coming from somewhere outside Excel.
How the numbers look in the data sheet doesn't matter. But I use this data to generate pivots, wherein the months are the columns. I want it to say Jan, Feb, etc. across the top of the pivot.
I realize that I could just loop through the raw data and convert the numbers to text. However, I want the pivot table to recognize this data as dates, so that I can do date-related stuff, such as the date grouping described on p. 85 of Jelen's Pivot Table Data Crunching book.
If I go into the raw data and try to convert the cells to the mmm format, they all convert to January.
I'm aware that formatting doesn't translate from raw data to pivot table anyhow. But it's not clear to me how I can use the date field grouping functions without somehow showing Excel that these are dates. Or is the pivot table smart enough to realize that just by the names, even if they are formatted as text?
I have a range of dates in c1:c285. I have a range of liters sold on those days in e1:e285. I want to total the liters sold per month for the 2 year period.
I was trying to use =SUM(IF(MONTH(c1:c285)=1,e1:e18,0))
However this just returns a "value" comment. I think even if this worked it would add the months together for the 2 years so both June figures would be returned as one figure.
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.
How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?