Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Calculate Total Sales from a Database
I wish to devise a formula that will Total hourly sales for locations, for
specific days from data within a databse I've pulled down from Excel. The following formula that will return the Total Sales for any specific Day C8=Specific Sales Date B6=Location SalesDate=Range name of Sales date in Database HOBO_Location=Range name of location in Database HOBO_Sales=Range name of Sales in Database =SUMPRODUCT(--(SalesDate=C$8)*(HOBO_Location=B6),((HOBO_Sales)))/100 What I want however is a Total per Hour for the formula above. My database breaks down sales into Qtr hour, so in effect I want to total 4 qtr's for one hour. In my Report I have the following layout Hour Ended Mon Sales Tues Sales Wed Sales 24/10/05 25/10/05 26/10/05 10:00am ?120.25 ?95.25 ?1,000.25 11:00am ?195.75 ?180.35 ?2,007.43 12:00pm ?250.90 ?75.60 ?120.15 1:00pm ?300.75 ?40.75 ?30.25 The formula I'm trying to devise for say, Monday at 11:00am, would be "Tot all sales that are within the database between A4 and A5" (A4=10:00am and A5=11:00am). The only complication is that the Times shown on the Database don't actually translate as 11:00am etc, but rather a value 1100. For this I've created two tables which in effect translate 1100 to equal 11:00am. So I guess there would have to be a Lookup function within the formula to match this If more info is needed just let me know Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate total interest earned on a loan and monthly payment | Excel Worksheet Functions | |||
how do i calculate 20% of a total figure | Excel Worksheet Functions | |||
How do I calculate a MTD total - yesterdays sales in Excel? | Excel Worksheet Functions | |||
calculate predicted sales value | Excel Worksheet Functions | |||
Pivot Table - Running Total Annualised | Excel Discussion (Misc queries) |