Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










SUM A Range Of Sales Based On Month


I am trying to add a specific range of data
Column A include a code
Column B-X 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:
Determining Top Contributors To 50% Of Sales Based On Cumulative Percent Of Sales
I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.

View Replies!   View Related
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 Code--Month1-Month2-Month3
ABC1----------43------33------19
ABC2-----------2------10------25
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
Pivot Table Show Selected Month And YTD Sales
I have created a Pivot Table to sumarize the monthly sales data.

Is it possible to select just one month and show the YTD sales?

e.g. We are in September, I select August the Pivot Table should show the August sales column and YTD (Jan to August) sales column.

View Replies!   View Related
Sum The Sales Revenue Column Of The Attached Per Month Into The Yellow Highlighted Cell @ The Bottom
is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?

View Replies!   View Related
Pivot Table: Calculate Percentage Of X Sales To To Total Sales
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.

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
Summarize Monthly Sales From Daily Sales
I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.

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)*C5-H5)/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 Oct-09 Nov-09 Jan-10 Feb-10
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
How To Calculate Sales Based On Partial Cell Content (multiple Inputs)
I have made a dynamic calculator which will calculate the total sale based on 3 criteria in 3 different cells as follows:-

I would like to derive the sales qty in cell C5 if all the 3 conditions in the range B2:B4 is true.

In this case the sales qty for Style-Colour-MRP combination(9103-PINK-399) should be 6.

I am unable to figure out with the formula for the same....

View Replies!   View Related
Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).

Range I4:I28 to the monthly sales worksheet by date.

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 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

View Replies!   View Related
Using Offset From Latest Month To Calculate 3-month Average Within A Range
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........


View Replies!   View Related
Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

View Replies!   View Related
Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)
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 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
Sales Growth Per Month Formula...</title><script Type="text/javascript" Src="clientscript/vbulletin_post_loader.js?v=384"></script><style Type="text/css" Id="vbulletin_showthread_css">
In my spreadsheet below I want to be able to enter a sales number for January, the value of cell F2.

I want cells F3 thru F12 to automatically calculate according to the "Growth Per Month" value in cell H1.

Example: If January sales are 20,000, then February should calculate to 21,000 (january * 105%).

Excel Jeanie HTMLSheet1

E F G H 1 2010 Per Month Sales XXX 5% 2 XXX XXX 3 February $ 10,000 4 March $ 5,000 XXX 5 April $ 2,500 6 May $ 1,250 7 June $ 625 8 July $ 313 9 XXX $ 156 10 September $ 78 XXX 11 October XXX 12 November $ 20 XXX 13 December $ 10 14 $ 39,990
Spreadsheet Formulas Cell Formula F3 =F2*10*H1 F4 =F3*10*H1 F5 =F4*10*H1 F6 =F5*10*H1 F7 =F6*10*H1 F8 =F7*10*H1 F9 =F8*10*H1 F10 =F9*10*H1 F11 =F10*10*H1 F12 =F11*10*H1 F13 =F12*10*H1 F14 =SUM(F2:F13)

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 Year-to- Date periods based on the month and date.

I need to automate the year-to-date 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 Jan-Sept 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
Conditional Formation Based On Month
I have a field where the month is written as text. I would like to highlight row whose month column has passed. My conditional formating code is:

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 01-07-07 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 10-31-08 into a2, or november 1, then put 11-30-08......)

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 happens-no 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 (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
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 month-end 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
Calculate Year To Date Based On Known Month
I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.

There is of course several ways to do this, but is there a simple and easy formula one can use.

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
Formula: Current Month Based On System Clock
Is There a formula for current Month based on system clock....

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
File Creation Based On Name Of Month Mentioned Columns
I have the a file with the below Columnar format:

CoName April May June... till Mar

I have 15 Company Names. I need to create a file for every CoName-Month Combination.

CoName April
CoName May, etc

I do not want to create 15x12=180 files manually.

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 January-December 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" (1-12)
"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 re-calculate 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 Apr-DEC. Currently I woud be required to change the range in the formual to take into account the extra cell required.

View Replies!   View Related
Formula To Count Weekday Occurrence Based On Month And Year
I know there's a pretty compley formula out there that counts the occurence of say Fridays in 2009 - does anyone have this? I had it before in a file but ranged valued the results showing the count of each day of the week for each month in a given year.

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
Copy/Paste Cell Value To Correponding Dates Based On Month
I want to paste a cell value into a column that is a certain month based on a date inserted by the user. For example, in cell a1 is £1000 in cell a2 is 01/01/2008 so i would then want this to be pasted in the same row and then column called january 2008.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved