SUMIF Month & Year: Find Total Cost By Month Only For Year 2009
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Using Month In Calculation ONLY If Current Year Is 2009.
I'm calculating sick days on an Attendance Report. - Sick days accumulate at a rate of 1.25 per month. - Employees will carry-forward unused sick days from 2008. - The maximum sick days is 30. In trying to create a formula that will multiple the current month (eg Dec=12) by 1.25, subtract any sick days taken YTD (S16), and add the carry-forward days from 2008 (W16), I noticed one problem with the following formula:
View Replies!
View Related
Message To Apear It The User Selects A Year AND Month Less Than The Current Year
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it. '....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then MsgBox ("You may not enter Data before the current Month") Else '...... Run main code here
View Replies!
View Related
Month Of Year
As To Why This Is Giving The Answer Of "January Of 2009"? For All Answers. Sheet7 RS92/27/2009January Of 2009102/28/2009January Of 2009113/1/2009January Of 2009123/2/2009January Of 2009133/3/2009January Of 2009143/4/2009January Of 2009 Spreadsheet FormulasCellFormulaS10=TEXT(MONTH(R10),"MMMM")&" Of "&YEAR(R10)S11=TEXT(MONTH(R11),"MMMM")&" Of "&YEAR(R11) Excel tables to the web >> Excel Jeanie HTML 4
View Replies!
View Related
Count By Year & Month
I am working on an excel sheet where I enter dates for registered events. I need to count the number of days registered for any month. For example, Column B contains date of event, column C contains number of events for that particular date. I need to get the following results: - How many dates registered in the Month of January (or any month), I assumed this is the number of dates that were entered for the month January. - How manu events registered in the Month of January (or any month), I assumed this is the sum of the numbers in column C that match the dates of the month January. I need the formula for this example because I have other counts I need to calculate per month, such as number of people registered, etc.
View Replies!
View Related
Formula: Day Into Month+year
i need to put a date into a month +year like this: 1/06/2008jun/08 i did this with a format date. After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date. MaandTotal jun/081jun/0810 he sees that 1/6/2008 is not the same as 2/6/2008 that is why i got 2 lines of jun but i just want one and that he counts all the junes together
View Replies!
View Related
Compile Results From Same Month/Year
I have to sheets sheet LOG sheet MONTHS Log contains in column a1 all the way down, 02/12/97 to 12/31/07 and all the dates between. Oh yeah COL F contains the Result that I am trying to find they are numbers, which I want to group by month year. What I currently have is on the same sheet a formula,,,, Sep-07 =SUMPRODUCT(--(MONTH($A$5:$A$1386)=9),--(YEAR($A$5:$A$1386)=2007),$F$5:$F$1386) Oct-07 =SUMPRODUCT(--(MONTH($A$5:$A$1426)=10),--(YEAR($A$5:$A$1426)=2007),$F$5:$F$1426) Nov-07 =SUMPRODUCT(--(MONTH($A$5:$A$1426)=11),--(YEAR($A$5:$A$1426)=2007),$F$5:$F$1426) and I have to change the month and year for all the month/years.. What I would like is on sheet Log in col a, the dates that I enter, but on the sheet MONTH. I would like lets say for example A1 cell contains SEP 07, I would like A2 to have a formula that looks at the month/year in A1 on MONTH sheet, then goes to LOG sheet searches down column A for the correct month/year (as per cell a1 Month sheet), and sums up all the number that in col F on LOG sheet.
View Replies!
View Related
Calculating Dates In Year And Month
I trying to find a formula to give the difference between two dates in year and month. For example, the start date is Feb 1 2006 and end date is Jan 1 2008. The formula should result in 2.11 I tried the following formula from a previous thread but it gives the answer in text but I need it to result in 2.11. [url]
View Replies!
View Related
Set Year And Month To Sheet
i need to set year and month to my sheet. the sheet works like a paycheck report. So if it´s posible to do this i can go back in time and do my report. now i use excel´s own date form serial number. so if i need to go back to 06 i need to type 2006-11-1 if i worked that day. is it posible to format the sheet by typing in the year and month and the hole sheet works after that input?
View Replies!
View Related
Countifs With Month And Year Function
I have a column titled "Start Date", "Month/Year", &"Total". I want to count the number of dates that appeared in the "start date" column that have the same month and year of the "month/year" column. That count should be in the total count. For ex, I want the first total (april 2008) to be 1, the second (may 2008) to be 1, and so on. I tried using countifs month and year of the start date is the month and year of the month/year column. Not working. Here's my data: ....
View Replies!
View Related
Match A Date To A Month-Year To Assign A PO#
My workbook has a worksheet that lists service activities and a 2nd worksheet that contains a month-year column and an associated Customer PO# to be assigned to an activity based on its Open Date...if it is a specific type of activity. My logic statement is IF activity type RO is "X", then match its Open Date to the Month-Year in the AssignPO# sheet and assign the associated Customer PO. I've attached a sample workbook to make it simpler to understand.
View Replies!
View Related
Return A Date Based On Year / Month
formula off here i use all the time relating to finding and sumproducts for specific months and years i.e. Jan 2008, Dec 2007.. depending on these dates excel searched through a specified range and returned me any values i wanted like No. of occurences, totals, sums etc etc it was a sumproduct formula... is there any way i could specifiy a date i.e. Jan 2009, which would search column a and return the date /and/or an account number in column b, only if the date was during jan 2009? The reason i want this is to use a lookup on the account numbers to return specific items of info, but i only need the account numbers if they occurred in specific months which i want to choose.
View Replies!
View Related
Finding Average Based On Month And Year
I have 5 years of data in 2 columns: Col A. Col B. 8/2/2004 Value 1 to 7/31/2009 Value n I have a table set up as follows 1 2 3 4 5 6 7 8 9 10 11 12 2004 2005 2006 2007 2008 2009 I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table... I tried the following to no avail... I'm just getting a zero value: =AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))
View Replies!
View Related
Check Month & Year Of Dates
I am trying to calculate the number of days it takes to complete a project when the project ends in a specific month. If it is July 2007 I want the number of days for each project completed in July 2007. The number of days is not the problem it is reflecting the number when a project takes longer than one month to complete. To add to things the users sometimes have "open" or "00/00/00" in E (for never close). I have a spreadsheet with dates a project begins in column D and dates the project ends in column E and a start and end date for each month going across rows 1 and 2. When the project starts and ends in the same month life is good and my original formal of greater than and less than the dates (courtesy of this website) is effective. I tried the following formula in F34. Each piece of the formula worked but when I combined it I either broke it or have too many nested formulas. =IF(OR($E34="OPEN",$E34="00/00/00",$E34>F$2),"",IF(AND(MONTH($D34)=MONTH($E34),IF($D34>F$1,$E34<F$2)),$A34,IF(AND(MONTH($D34)>MONTH($E34),if(MONTH($E34)=(MONTH(F$2)-1)),$A34,"")))) I created a work around by putting formulas in B and C and row 3 that would answer some of the nested if and working off the results. I was hoping someone might be able to help me make this a little more effecient.
View Replies!
View Related
Trim Month Year For Conditional Formatting
Having trouble with a date format: I am trying to get CurrentDate from a 02/07/07 to 02/07 My CurrentDate = Format(CurrentDate, "mm/yy") doesn't seem to work, no matter what CurrentDate = mm/dd/yy My eyes are watering from stareing at the screen for so long.... Private Sub TextBox24_Change() Dim MonthPlus, CurrentDate As Date Dim userow, usercolumn As String
View Replies!
View Related
Multi Conditional Formula For Year And Month
I am experiencing a big block on this one...I am trying to find the sum for a group of cells in a particular column given that MONTH and YEAR (as stated in two separate columns) match the date that is displayed in, lets say for example, A2. below is a sample of the data I am refering to. ************************************************************************>Microsoft Excel - Investor_Portfolio_TEMPLATE.XLS___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA12= ABCDEF1DateAmountYearMonthSumofAmount23/21/05990002005Mar33/21/0599000Apr46/8/0593000May56/9/0599000Jun63/23/0599000Jul73/23/0599000Aug81/4/0699000Sep91/5/0699000Oct101/6/0699000Nov112/1/0699000Dec122/5/06990002006Jan134/5/0699000Feb144/7/0699000Mar155/2/0699000Apr165/8/0699000MaySheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.[/url][/code]
View Replies!
View Related
Date Display Like Day/Month/Year
Im having a little issue with the way the dates are layed out. I have a condition format =AND(RC5="No",TODAY()<RC4+10) And withen the cell's the date is located, If the cell is writen Month/Day/Year everythink works. Though i have always written dates like Day/Month/Year. ive tryed to do go to the number format thing and change it, though that just seams to change the end display not the way excel handles the date. it there a system level change that can be made of change somethink.
View Replies!
View Related
Choose Year To Date And Month Figures?
I am creating a worbook to generate month end reports. I'd like to pull figures from the various budget sheets into the reporting sheets for the current month and year to date by setting up a variables sheet and I think the "Choose" function but my experimentations have proven me incapable of doing so. What I'd like to do is set my month in the variables sheet to the month I want to report in, eg "Feb" and have the budgets pull through automatically.
View Replies!
View Related
Converting A Day Date To A Month And Year
I am trying to use a pivot table to analyse some data. The base contains a "day" date..ie 01/05/08, however I want the pivot table to summarise by the month..ie May 08. I have used the "=month(cellref) command, and it returns the correct month number. However when I try and format this to "mmm" or "mmm yr" to get a month I can then cut and paste, it always retunrs the month of JAN.
View Replies!
View Related
Summarize By Month & Year To Date
I have data which I import from CSV files from a JDE application on a monthly basis, using "JobCostData" macro ( have not posted code here as it is contained in the attached file). For each month I import the data my macro creates a seperate spreadsheet and names the spreadsheet as the "mmm-yy" the data relates to, and then takes the information from each sheet and collates this on to on spreadsheet named " Pivot Data". I then run a second macro "MacroXX", ( again have not posted code here as it is contained in the attached file), this macro looks at the data on "Pivot Data" and creates a unique list of Work Order Numbers and Descriptions and copies them to the spreadsheet named "Report". Now what I would like to do (and can't get my head around): Once the "Report" has been populated I would like to come up with a series of total "Cost $" for each Work Order by: 1. Month, for the past 6 months based on a Calendar Month End date (Note: For now I have hard coded the dates, but I will add code at a later stage to prompt user to select a date which will then write this to cell H4 on the "Report" spreadsheet and have the other dates derived from this date.) 2. Year to Date (YTD) based on the Financial year the Calendar Month End Date falls 3. Inception to Date (ITD) based on the Note: 1. I have tried using Pivot Tables but this will not give me ITD totals. 2. I would prefer to use a macro rather than formulas. 3. I have populated the "Report" spreadsheet with the expect result based on the current data.
View Replies!
View Related
Year,month,week,days Calculation
What I am trying to to is calculate the number of Years, Months, Weeks, and Days from one date to another. So far I can calculate years and months accuretly but I'm having trouble with the days and can't seem to figure out how to do the weeks. I'm using the formula: =DATEDIF(B1,B2,"y")&" Year(s), "&MOD(DATEDIF(B1,B2,"m"),12)&" Month(s), and "&(MOD(DATEDIF(B1,B2,"d"),365))&" day(s)" B1 is the current days date B2 is the entered date What I am getting when I enter the date 9/14/09 with the current date (8/3/06) is 3 years, 1 month and 43 days. when it shoud only be 3 years 1 month and 11 days. Any date I enter the days are not right. I can't seem to figure out what I am doing wrong. Also I'd like to get the weeks to come up also. Such as Start date 8/3/06 Entered date 10/19/09. What I want to see: 3 year(s), 2 month(s), 2 week(s), 2 day(s).
View Replies!
View Related
Display Month & Year Only Of Dates
If I have a date which is 03/09/2006. How can I create a formulae to abbreviate this. Ie to return 09/2006 (I am english so we have the DD and MM the other way round to you guys in the states). In other words if an event happens on the third of september 2006 I want a column which classifies that event as september 2006 with NO reference to the day.
View Replies!
View Related
Collect Month & Year From User
Date = InputBox("Enter the production month date in YYYYMM format", "DATE") 'This is the only info I want them to enter Windows("PCN4.xls").Activate Sheets("Bring the data in here").Select Range("A" & NextLine).Select With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;W:C30MonthlyMonthly NEW TCs200610 otconsa.txt", Destination:=Range _ ' I only need the 200610 to change every month ("A" & NextLine)) .Name = "totconsa" End With
View Replies!
View Related
Average With Conditional Month, Year And Date Range
I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 average-which would be the average of numbers found between Jan21-Feb20, I then need Mar 08 average which would be data from Feb21-Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc). As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.
View Replies!
View Related
Pull The Month And Year No Matter What Format The Dates Are In
I have a data chart that lists dates in various types of formats, for example: 1/1/10 1/1/09 Jan Jan '10 Jan/Feb '10 Jan/Feb I'm trying to figure out a formula that will just pull the month and year no matter what format the dates are in. Based on the above I'm looking for the following results: 1/1/10: Jan 10 1/1/09: Jan 09 Jan: Jan 09 Jan '10: Jan 10 Jan/Feb '10: Jan 10 Jan/Feb: Jan 09 This data is for the entire year so the same applies for all months.
View Replies!
View Related
Get Month & Year X Months From Current Date
I am trying to get mmm-yy date format for the next 2 consecutive months from now. I used the following code for that reason, but I am not getting. strDate = Format(DateAdd("m", 1, Date), "mmm-yy") strDate = Format(DateAdd("m", 2, Date), "mmm-yy") However if I use this, I get the current months date correctly (like May - 08) strDate = Format(DateAdd("m", 0, Date), "mmm-yy")
View Replies!
View Related
Change The Cell Focus Based On Month And Year
I have a worksheet called "zz_Exchange" that contains the exchange rate for my company for each month of the financial year. I am using this info in a summary page that uses the data for multiple calculations in the workbook.As we pass each month I would like to move the focus of a cell in the summary sheet to the relevant month in "zz_Exchange" The column names are "currency_code" (USA Euro etc), "currency_year" (09, 10) "period" (1-12) "exchange_rate" (this is where the data is held)
View Replies!
View Related
Sum Report Based On Month & Year
I would like to calculate the sum of investments based on their expiry date and have the totals per month (and year). I have a table that looks like: TermInterest rateMaturityBalance 24 Months7.12%11 November 200740,000.00 12 Months7.74%13 November 200750,000.00 24 Months7.05%10 January 200853,889.12 12 Months7.85%11 January 2008120,000.00 12 Months8.02%22 March 200817,000.00 36 Months6.68%30 June 200832,000.00 I'd like to have something like: Nov 07 90,000.00 Dec 07 0.00 Jan 08 173889.12 and so on... Admittedly I am an Excel novice, so excuse me if my question is dumb and has a simple answer (actually I hope it has :-) but I have tried to find a solution by searching forums, my books, online help, I tried my luck with sumif and SUMPRODUCT functions, even used the conditional sum wizard, but I can't get it right
View Replies!
View Related
Sum Column Based On Other Columns Year & Month
I have the following variables in these columns Column 1: Ship (1064, 1065, 1066 as the field contents) Column 12: Date (21-Feb-08 as format) Column 13: Weld Length (1000 as format) Column 15: Defect Length (1000 as format) What I need doing is the following is in a single cell per month add up what the total weld length is as well as the defect length as I have Jan 08, Feb 08 etc on another sheet where these values will be returned. There is a seperate sheet for each Ship so would like a formula that I could ammend 1064 to 1065 etc
View Replies!
View Related
Calendar From Formula :: Nth Day Of Week For A Month And Year
I'm using this formula from [url] Nth Day Of Week For A Month And Year This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7. =DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+ (DoW-WEEKDAY(DATE(Yr,Mon,1)))) Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week. I would like to be able to change the year and month in A2 and B2 and have the calendar change. I will be inserting rows between the weeks to return appts, if I can get this part working. I could make a new tab for each month, but I thought I would give this a try....
View Replies!
View Related
|
TRACKER
