SUM A Range Of Sales Based On Month
I am trying to add a specific range of data
Column A include a code
Column BX include actual data
Culumn X AI include budget figures.
Also in cell A1i have the number of the month
For example the month is 3 (March)
I want in AK to create a SUMIF where the formula will sum columnsX+Y+Z
If month goes 4 then should calculate
X+Y+Z+AA
and so on
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
SQL To Excel  Stock Sales By Month
I am fairly new to VBA / Excel programming. I have been trying to write a report out of excel from our company DB (SQL2005). The database is run by our frontend accounting application  so i cant mess with it at all, must only run queries. I need to pull the last 24 months of stock sales data(by stock code or category) out of our DB into excel by counting transactions on Customer Invoices / credits. Into a table as follows.. Stock CodeMonth1Month2Month3 ABC1433319 ABC221025 I have managed to make a script that fullfills this need but it takes about 15 minutes to run(Due to having to loop many times per item/ per month).... I was just wondering if anyone had any tips / advice on different ways to do this..??? Ive had a quick look at Pivottables but havent gone very far in, maybe they are the answer, but this amateur does not know.
View Replies!
View Related
Formula To Calculate Sales Tax From Total Sales
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and filling in the chart on excel but I would like to create a formula that automatically does it for me based on total sales.
View Replies!
View Related
SumIf: Keep Stocks, Based On 2 X The Sales
As you can see I am using the code below in ( I ) =IF(OR(G5="",H5=""),"",INT((INT(2*G5/C5)*C5H5)/C5)*C5) What I am trying to do is keep stocks, based on 2 x the sales, as you can see G5 I have 15 I still have stock of 35 so I should not need any stock but it has put 50 in. The 50 is a layer rate that I need to order in if I need any. If I had 20 sales and 19 in stock I would want it to order 50. It is the same for all the ones listed in the sheet apart from I8 where it should have ordered but only 40.
View Replies!
View Related
Formula That Will Determine The Number Of Days That Fall In A Specific Month Based On A Date Range
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 Oct09 Nov09 Jan10 Feb10 10/15/2009 01/13/2009 I'm using Excel 2007.
View Replies!
View Related
Function To Fill All Days Of Month To End Of Month Based On Workdays
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.
View Replies!
View Related
Date Range Formula: Beginning Of Month To End Of Month (which Is In The Current Row)
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 midday and midmonth. 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
View Replies!
View Related
List Of Sales By Salesperson From A List Of All Sales
I have one sheet that shows a list of all vehicle sales for a month: with a customer column and a salesperson column and a gross profit column. I would like to give a printout to each salesperson from a different sheet that only shows that salespersons transactions on it. Can excel parse that information out and list it in order row by row showing each sale for just one salesperson per sheet?
View Replies!
View Related
Sum Based On Month Criteria
I maintain a table with projects and their respective costs / revenues. I have a formula that automatically sets the forecast and Yearto Date periods based on the month and date. I need to automate the yeartodate sums such that, when the date changes and a new month acquires the YTD status, that the monthly costs/revenue of the projects are updated e.g sum of JanSept in the YTD column(for this month). A sample workbook is attached.
View Replies!
View Related
Sum Based On Month Of Corresponding Dates
I have a worksheet of data I need to sum based on a monthly date range criteria onto a separate summary worksheet. Both are in the same workbook. I tried using SUMIF and SUMPRODUCT but can't seem to get the criteria correct when I add in LEFT into the argument for the date criteria "6/" or "06/". Here's where I'm at so far: =SUMIF('2008'!E4:E149,(LEFT('2008'!E4:E149)="06/"),'2008'!I4:I149) My table looks something like: Log NoShip DateQty 000953/26/20085 000963/28/20081 000974/28/20081 000985/28/20082 000995/29/20081 001006/1/2008 001016/14/20084 001026/20/20083 The 6/1/08 entry is intentionaly blank to be filled in as the value becomes known and then the totals would of course need to be updated.
View Replies!
View Related
Counting Entries Based On Month
I have a column of dates and want to count how many times there is an entry for a particular month. example: (column D) 7/4/2009 7/13/2009 7/22/2009 8/1/2009 8/6/2009 9/15/2009 I want to be able to coun (countif?) how many times there is a July listing, August listing, etc. =COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009") =SUMPRODUCT((D3:D20>="7/1/2009")*(D3:D20<="7/31/2009")) I have made them arrays.
View Replies!
View Related
Average Based On Criteria :: Within Each Month
I have a list of ages of people who cancelled their account. I'd like to find the average age of people within each month. So column A = age and column B = cancel date Let's so for January 2006 I'd like to find the average age. Logically this is how I see it... Average Column A (If column B >= 1/1/2006 and < 2/1/2006)
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 Based On Text & Month Criteria
I am trying to get the sum of some cells (integer varies in column G), but comparing one column content (exact) and dates in a different column. I tried the following: =SUM((Tony!G30:G44)*(Tony!D30:D44="Crazy")*(MONTH(Tony!E30:E44)=7)) Column E would contain a date, such as 010707 or 1st July 2007. In the D Column, keywords such as "Crazy" are concise and standard. However regarding dates, am I better off finding a formula that looks for cell content (Contains "july", as opposed to ="July"), or using a month function (but getting it to work)? How can I do this?
View Replies!
View Related
Automatically Adding Days Based Off Month
I am trying to automatically add the last day of the month based off of the month for example a1 = beginning date a2 = end date a1 will always be the 1st day of a given month then based off a1, know the last day,(i.e.... october 1, then put 103108 into a2, or november 1, then put 113008......)
View Replies!
View Related
Print Correct Chart Based On Last Month
to print last months results each month. I have a cell (range name = LastMonth) with formula =now()15 to display last months full name since I will always do this in the first half of each current month. I want it to look at that cell on the "FY" sheet, see what month is displayed then go to the "Monthly Audit By Cell" sheet & print the correct chart. Each chart is named "Jan Audit" and so forth I have this  only first six months done so far. Problem is nothing happensno printing! Doing this today on June 14th should print out the May chart. ' Print the Audit for the current month
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 (21Feb08 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
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
Total Days In The Month Based On Date
is there a formula that will say how many days there are in a month ? B9 contains the a refrence to the first day of the month eg 1/7/2008 I want the cell above it (B8) to return 31. If the date reference is 1/6/2008 I want (B8) to return 30. I'm using it to pro rata by the number of days in a month.
View Replies!
View Related
The Next 12 Month End Dates Based On Today's Date
I am trying to project the next 12 monthend dates, based on today's date. I can do that using the EOMONTH function ... see exhibit below ... present month, 1 month out, 2 months out, last month. However, this workbook must be sent to many people and many of those folks will not have EOMONTH functionality because that requires the Analysis Toolpak functions to be added in. How can I accomplish this using standard Excel functions? Present Month >>> =DATE(YEAR(NOW()),MONTH(NOW()),1) One Month Out >>> =DATE(YEAR(EOMONTH(NOW(),1)),MONTH(EOMONTH(NOW(),1)),1) Two Months Out >>> =DATE(YEAR(EOMONTH(NOW(),2)),MONTH(EOMONTH(NOW(),2)),1) Eleven Months Out >>> =DATE(YEAR(EOMONTH(NOW(),11)),MONTH(EOMONTH(NOW(),11)),1)
View Replies!
View Related
Update Day Of Week And Date Based On Month
I'm attempting to force excel to auto update the day of the week, and the date in a spreadsheet. The date isn't as important, since it can be hard coded. The only problem there is some months have 31 days, some 30, and another with 28. I've uploaded an image of the spreadsheet, and you can see in field A1 the date/year is input. I'm wanting to find a way to force the days/dates in fields 2E and 3E to update based on the month.
View Replies!
View Related
Vba To Get Next Balance Based On Date And Month Cycle
I have dates of the record add date to a database, and balance date cycle month. What i require is loop through each record and return the next month end date of the balance date cycle month of the add date for each record. eg. added to DB  balance date cycle month  Next balance Date 05 March 2004  April  30 April 2004 18 April 2003  March  31 March 2004 Refer sample file attached. I have populated the Next Balance date with the result required.
View Replies!
View Related
Show Value From A Table On Second Sheet Based On Month
I need to display a value that is in a table on one sheet when the month is matched on the first sheet. The month that I need to match up is on sheet1 in cell B1 I have a table on sheet2 that is a year budget, the cell range for the months are C5:Y5 Under each month I have a list of headings and it is these cells that I need to display on sheet1 in the relevant box.
View Replies!
View Related
Display Data Based On Current Month
I have a tab set up to display information that has been colletect on other tabs. What I would like to do is change this information depending on what month is selected. For example, if I select December then data would be pulled from other tabs and displayed. If I Select June the information would change and select other data from the same tabs.
View Replies!
View Related
Average Based On Time & Month Cells
How do I average the data in column G, based on two conditions (time in column H = $H$1, month in column I = month in $I$1) Here's the formula I came up with, but it is including blank cells in the calculation. { =AVERAGE (IF ($H$3:$H$100=H1, IF (MONTH ($I$3:$I$100) = MONTH(I1), $G$3:$G$100, FALSE), FALSE)) }
View Replies!
View Related
Calculation Value Based On Month Name In Adjacent Cell
I have a spreadsheet spread over with dates in column A from September to December and I have a fuel rebate column as well. The fuel rebate is different for each month and is calculated as a certain amount of cents per dollar added to the total amount in Column B September: 26c October: 13.5c November: 43c December: 66c e.g. If there is an invoice for 10/9/07 (september) for $100, then the fuel rebate would be $100*.26 which equals $26 fuel rebate. I want to calculate column B (this is the column with the dollar amount in it), but I want the calculation to be different depending on what date is in column A, so if in column A the dated month is October, then the calculation would be something like B2*0.135. give me the calculation I am certain you don't need VBA for this right (just nested IF statements?)
View Replies!
View Related
Changing Column Letters In Formulas Based On Month
I'm trying to update a series of different sheets in a book based on what month I have filled in data for. For example, at September Month End, I will fill in data vertically. Now, I have different tabs that reference certain cells in that column. There row number never changes, but once a month I have to go through and increase their column letters by 1, and there are a lot of places to update. Can anyone lend me some ideas or a solution to how I should go about using formula/macro to update these cells.
View Replies!
View Related
Calculate Number Of Dates Within A Column Based On Month
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything. So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.
View Replies!
View Related
Flowing Month Data Into A Chart Based On Weeks?
I have a spreadsheet that I am using to try to track Project Man Hours. What happens is: I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need. i.e. Project Start  January 2009 Phase 1  4 weeks with 1.5 Men Phase 2  20 weeks with 2 Men. What I want to happen is: Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase. Project 1 showing a line chart with JanuaryDecember and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....
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" (112) "exchange_rate" (this is where the data is held)
View Replies!
View Related
Copy & Paste Data Based On Current Month
I have created an action plan in Excel, with a column containing the values Yes, No and Partial to show prgress against the actions. I have used CountIF (in cells D72 to D74) to count the number of each Yes, No and Partial. I want to be able to put these figures in a chart each month. I am using columns F to Q for the chart data. the headings of the chart columns are month values, e.g, F1 is "6" (for June). I have used Month(Now()) to generate the current month value in cell B1. I now need a macro that will check the value in B1 against the chart table headings in F1 to Q1 and if it finds a match, will copy the values D72 to D74 into the chart table below the relevant heading.
View Replies!
View Related
Increase The Range Each Month.
I currently have an excel spreadsheet which has monhtly sales totals in it. We have some forumla that compare Year To Date figures with YTD Budgets and we also wish to compare Current Year To Date to Previous Year To Date. The problem I have now is that it currently means updating the range formula for Year to Date Budget and I will also be required to update the Previous Year To Date sales manually too. Im looking for the spreadsheet to recalculate theses figures based on the month that they are in. For instance should I be working on the Spreadsheet in January, it will only use the values in the budget sheet from AprDEC. Currently I woud be required to change the range in the formual to take into account the extra cell required.
View Replies!
View Related
Automatic Entry Of Dates Based On Month & Year In Cells
I want to create a monthly timesheet which contains 9 columns for (Date, Day, Project no., Activity, Time In, Time Out, Total Hours, OT Hours, Remarks) I have used IF Function to calculate Total Hours & OT hours automatically. Time IN & Time OUT, Project No., will be entered manually on daily basis. Weekday function is used in the Day column to return the corresponding day of the date in the Date column. Name of the Month and Year will be manually entered in the designated cells I3 and I4 respectively. Now the solution I am looking for is, the dates should be automatically entered in the Date column (in cells A8 to A38) based on the Month & Year entered in cells I3 & I4. Dates of the corresponding month of the year should only be filled in. (If a month is not having 29, 30 or 31st day, the corresponding cells should be left blank. i.e. nothing should be displayed in the corresponding cells). I am looking for some sort of formula to enter in the cells of Date column (A8 to A38) achieve this. I have searched the forum and could not find anything which could at least give me an idea about the kind of function or formula to be used. Attached here is the time sheet I am trying to create.
View Replies!
View Related
