Copy Week Total In Weekly Sales Worksheet To Appropriate Week In Monthly Sales

Oct 14, 2009

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


ADVERTISEMENT

Using Rolling 12 Month Sales And Minimum Monthly Sales?

Oct 17, 2013

I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....

The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.

Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....

View 6 Replies View Related

Sales - Value Of Deals That Were Quoted And Closed In Same Week

May 12, 2014

I have a data set of Purchase Order information (each PO on one line) that spans from 2013-YTD and includes the following fields.

P.O. Amount
P.O. Issue Date
P.O. Close Date

I want to know how much P.O. Amount was "Issued" in the same week in which it was "Closed".

I would like to derive the following from that information:

Average of P.O. Amount that is Issued and Closed in the same week (in % and $ terms),
Count of P.O. Amount Issued and Closed in the same week.

I don't know how to aggregate the information to know how much P.O. Amount was Issued and Closed in the same week.

View 2 Replies View Related

Counting Week To Date Sales By Branch

Jun 9, 2009

I have to produce a report which shows how many sales each of my company's eight branches has sold this week. The problem lies in the fact that my manager wants the week to start on a Friday, so effectively I need to count the number of sales for each branch since the last friday (including Friday but not today, ie today's report would count all sales from 05/06/2009 to 08/06/2009).

Is this possible?

My data is as follows: Branch name is in column P, Date Sold is in column S. I have added a column which is next to Date in column T which has "=WEEKDAY(S2)" to give me the week number which I was using to try and count but failed miserably!

View 9 Replies View Related

Summarize Monthly Sales From Daily Sales

Jan 5, 2010

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 4 Replies View Related

Pivot Table: Calculate Percentage Of X Sales To To Total Sales

Feb 20, 2008

See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.

View 2 Replies View Related

Excel 2010 :: Sales Daily Sales Data To Be Aggregated Into Monthly Data (not Using Pivot Table)

Mar 27, 2013

I cannot solve with Excel 2010 and I have searched all over for the answer.

I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.

For example, I would like to turn this:

3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx

Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)

I have a feeling some 'countif' formula would work but I am not sure how to do this.

View 4 Replies View Related

Formula To Calculate Sales Tax From Total Sales

Jan 4, 2005

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 9 Replies View Related

Conditional Formula- Worksheet With Monthly Sales Figures

Aug 5, 2008

I have a worksheet with monthly sales figures by associate and by store. The store has a monthly goal as do the associates. If the store hits it's goal then the overall sales total is multiplied by 1% and then divided by the percentage of each associates involvement to reach that goal. (ie...150,000*1%=1,500, John sold 35,000=23%, so John gets $345 extra commission). If Johns goal was $25,000 and sold $35,000 he gets 1% or $350 commission. In turn, if he meets 1 or both sets of criteria those will be added together. If he doesn't meet either one then the result is Zero.

I have the store goal and Johns goal in separate cells to reference against. The actual sales cell is formula based.

This is basically what i'm trying to do:
If criteria 1 is met then % of 1% of store goal, if criteria 2 is met then 1% of individual goal, if both are met result1+result2. if neither is met then zero. I think?

View 9 Replies View Related

Finding Current Month Total Sales Using Current Month To Date Sales In Formula / Macro?

Aug 20, 2013

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

View 1 Replies View Related

Using The SUM Function To Record Weekly Sales

Nov 14, 2007

I would like to have a set of cells that add up all the sales within a given week. I know how to do this simply for one week, but how do I get Excel to automatically take this function and create the rest for future weeks?

After entering the SUM function in one cell, I click and drag on the box to try to get Excel to correctly input the functions in the next cells (like how Excel will correctly input the next date, week, or month). But Excel doesn't do it correctly.

View 10 Replies View Related

Calculate Weekly Sales With A Midweek Start

Dec 9, 2007

I'm trying to create a simple sales report. No VBA code, only excel formulas.
I'm stuck on trying to calculate the weekly sales. I want excel to be able to recognize the day of the week and know that the month started mid week.

Ex. If the 1st of the month started on a Wednesday, it adds all the sales from Wednesday to Saturday only and
if the month ends on a Tuesday, it will calculate the sales from Sunday to Tuesday only.
I want it done automatically.

I've included a zipped excel sheet example of the worksheet for a visual example.

View 9 Replies View Related

Sales Summary Sheet - Consolidate All Data In Sales Order Number Wise

Jan 5, 2012

I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....

All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.

Column A Column B
201 $200
202 $300
203 $450

View 8 Replies View Related

Determining Top Contributors To 50% Of Sales Based On Cumulative Percent Of Sales

Jul 2, 2009

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 3 Replies View Related

Sales Report From Sales Data Sheet If Select Date

Feb 4, 2013

I wanted to get the data from data sheet where i have entered the data manually for sales what have done

Now I wanted is when i enter date i should get the product and price in other sheet

Example:

If I enter date in a1 cell

I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet

And c1 - c10 price (if i sold 10 item @that price)

View 4 Replies View Related

Subtracting Monthly Sales From Constant?

Dec 12, 2013

I have sales per month going down a column and I would like to subtract a corresponding $ amount going across columns. It's hard to explain but I would like a formula that I can populate all of the cells with. Spreadsheet should make it clearer.

View 6 Replies View Related

How To Add Monthly Sales Data Using Formula

Dec 29, 2013

How can I add data to the monthly sales section in the workbook? I can't seem to figure out the code.

mockup.xlsx

View 5 Replies View Related

Calculate Monthly Sales Based On Data Given?

Jun 9, 2014

How to calculate the monthly sales based on the data given ?

View 4 Replies View Related

Daily Sales Target Based On Yearly And Monthly

Jan 7, 2014

I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.

I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .

I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .

View 2 Replies View Related

Get 5 Top Sales People Names And Their Sales?

Oct 3, 2012

In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.

Starting in cell B59 and C59 I'd like to display their name and sales respectively.

Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.

View 9 Replies View Related

Total Sales Of Every Month

Jan 14, 2013

I got individual dates in Column A. The format is 01/04/2012, 02/04/2012, 03/04/2012 and so on.

The person enters sales everyday. Now I want to make a new sheet which should sum up the sales month wise.

So in the new sheet I will have

Column A- Products
Column B- Sum of days in April 2012
Column C- Sum of Days in May 2012
Column D- Sum of Days June 2012
And so on

View 5 Replies View Related

Countif To Find Total Sales

Jan 19, 2009

I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product.

Countif(Sales!B:B,"Dave") works, counting all the instances of Dave.

But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for.

While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?

View 9 Replies View Related

Calculate Running Total Of Ticket Sales With 5 Different Groups?

Jun 17, 2014

I'm trying to calculate a running total of ticket sales with 5 different groups (adult, youth, senior day, senior night, and child) to where once the number of tickets are bought it will update automatically. I enter the tickets in cells B5-B9 where it then calculates the amount due and the change due. I need to put the running total in cells B18-B22.

View 3 Replies View Related

How To Create A Report To Show TOP 20% Based On Sales To Grand Total

Mar 5, 2012

Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.

How can I create a report to show the TOP 20% based on sales to grand total.

Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?

Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.

View 3 Replies View Related

Calculating Average Dollar Increase In Sales To Running Total

Jun 7, 2014

I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.

So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.

View 2 Replies View Related

Pivot Table Calculated Field To Subtract Sales Between Two Periods (not Grand Total)

Sep 1, 2011

'Pivot Table Calculated Fields" - Below is my pivot table.

I need the pivot table to subtract 'February' sales figures from 'January' sales figures to get the difference (variance).

I know the following steps:

1) Click on the Pivot Table

2) Choose "Formulas"

3) Choose "Calculated Field"

4) In the "Name" field - type in the name that I want "Two Month Variance" (for example)

5)......then I don't know what to do

Sum of Quantitymonthsupplier idjanfebGrand Total
754466163150842519337568263901785022875682639410025035075682639500
75682639642523866375682639780012492049756826398209209756826399292292
756826400264264756826401178178757494037292292761034919209209867233456264264(blank)
Grand Total245154236931

View 9 Replies View Related

Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.

May 23, 2006

I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.

Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.

I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?

Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?

attached is book.xls and sales summary.xls for reference.

View 3 Replies View Related

Delivery Report Summary - Monthly Total Sheet On 4 Individual Weekly Sheets?

Mar 3, 2014

We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.

View 3 Replies View Related

Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets

Jan 19, 2010

I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).

Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................

View 9 Replies View Related

Auto-updating Formula For Week Over Week Change?

Dec 11, 2013

I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.

What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved