Help, it's late and I am being thick

[Matthew]

I have a load of data 368X104 cells.......
This data is sales data by day per site.

Within this is also an area number so fo example:
a b c
1 Site No1 Site No2
2 Area No Area No
3 date1 Sales Sales
4 date2 sales Sales
5 date3 sales Sales
all i want to do is sum by area the vlaue of sales per day, the simple
way is a {sum(if......))} but I can not make this work for horizontal
not verticle. I am sure ther is a sipmple way, if you transpose then
you hit the 256 difficulty...


If there is anybody awake please help me

Regards

Matthew

 

[Max]

.. If there is anybody awake ..
Ah, half the world is awake at any one time

Assume real dates in A3:A10, Site#s in B1:C1, Area#s in B2:C2

If the Area#s are unique,
Assume that for an Area# input in A20
you want the total sales for Jan 2008

In B20, normal ENTER:
=SUMPRODUCT(--(TEXT(A3:A10,"mmmyy")="Jan08"),OFFSET(A3:A10,,MATCH(A20,B2:C2,0)))

If the Area#s are not unique, eg you may have the same area# in different
sites, then the uniqueness can be defined by using both Site# & Area#

In A20: Site#
In B20: Area#

Then in C20, normal ENTER:
=SUMPRODUCT(--(TEXT(A3:A10,"mmmyy")="Jan08"),OFFSET(A3:A10,,MATCH(A20&"#"&B20,B1:C1&"#"&B2:C2,0)))

Adapt to suit

P/s: For future new postings, pl use relevant phrases in your subject line
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik

 

[JLatham]

Max has provided a formula that should work for you very well, if you'd like
to try it with a user defined function (UDF), I wrote one up for you. To put
it into a workbook, open the workbook, press [Alt]+[F11] to get into the VB
Editor, then choose Insert | Module from the VBE menu and copy and paste the
code into the module provided. Make any changes needed to the Const values
to match the layout of your worksheet. How to call it is included as
comments in the code:

Function SalesByAreaPerDay(SalesArea As Variant, _
SalesDate As Date) As Currency
'call from a worksheet cell as:
' manual inputs:
' =salesbyareaperday(5,"9/2/2008")
'using cell references with appropriate values, as
' =salesbyareaperday(A1,A2)
'where A1 contains area ID to match
'and A2 contains date to match

Const salesDatecol = "A" ' column with dates in it
Const firstAreaEntry = "B2"
Const firstDateEntry = "A3"

Dim lastColumn As Integer
Dim dateRow As Long
Dim datesList As Range
Dim anyDateEntry As Range
Dim areasList As Range
Dim anyAreaEntry As Range
Dim totalSales As Currency

'set default return value to zero
SalesByAreaPerDay = 0
'find out how many columns we have to look at
'assumes that area IDs are in row 2
'and that A2 is an empty cell, and first
'area ID is in B2
lastColumn = Range(firstAreaEntry).Offset(0, _
Columns.Count - Range(firstAreaEntry).Column). _
End(xlToLeft).Column
If lastColumn = 1 Then
lastColumn = Columns.Count
End If
Set areasList = Range(firstAreaEntry & ":" & _
Cells(Range(firstAreaEntry).Row, lastColumn).Address)

Set datesList = Range(firstDateEntry & ":" & _
Range(salesDatecol & Rows.Count).End(xlUp).Address)

dateRow = 0 ' initialize
'assumes each date is only entered once
For Each anyDateEntry In datesList
If anyDateEntry = SalesDate Then
dateRow = anyDateEntry.Row
Exit For
End If
Next
If dateRow = 0 Then
Exit Function
End If
'we have date match, find all
'area matches
For Each anyAreaEntry In areasList
If anyAreaEntry = SalesArea Then
SalesByAreaPerDay = _
SalesByAreaPerDay + Cells(dateRow, _
anyAreaEntry.Column)
End If
Next
End Function

 

[JLatham]

Oops, last minute change made boo-boo. Use this instead if you use it at all.

Function SalesByAreaPerDay(SalesArea As Variant, _
SalesDate As Date) As Currency
'call from a worksheet cell as:
' manual inputs:
' =salesbyareaperday(5,"9/2/2008")
'using cell references with appropriate values, as
' =salesbyareaperday(A1,A2)
'where A1 contains area ID to match
'and A2 contains date to match

Const salesDatecol = "A" ' column with dates in it
Const firstAreaEntry = "B2"
Const firstDateEntry = "A3"

Dim lastColumn As Integer
Dim dateRow As Long
Dim datesList As Range
Dim anyDateEntry As Range
Dim areasList As Range
Dim anyAreaEntry As Range
Dim totalSales As Currency

'set default return value to zero
SalesByAreaPerDay = 0
'find out how many columns we have to look at
'assumes that area IDs are in row 2
'and that A2 is an empty cell, and first
'area ID is in B2
lastColumn = Range(firstAreaEntry).Offset(0, _
Columns.Count - Range(firstAreaEntry).Column). _
End(xlToLeft).Column
If lastColumn <= Range(firstAreaEntry).Column Then
lastColumn = Columns.Count
End If
Set areasList = Range(firstAreaEntry & ":" & _
Cells(Range(firstAreaEntry).Row, lastColumn).Address)

Set datesList = Range(firstDateEntry & ":" & _
Range(salesDatecol & Rows.Count).End(xlUp).Address)

dateRow = 0 ' initialize
'assumes each date is only entered once
For Each anyDateEntry In datesList
If anyDateEntry = SalesDate Then
dateRow = anyDateEntry.Row
Exit For
End If
Next
If dateRow = 0 Then
Exit Function
End If
'we have date match, find all
'area matches
For Each anyAreaEntry In areasList
If anyAreaEntry = SalesArea Then
SalesByAreaPerDay = _
SalesByAreaPerDay + Cells(dateRow, _
anyAreaEntry.Column)
End If
Next
End Function

 

[Sandy Mann]

Ah, half the world is awake at any one time

Or in my case half-awake at any one time <g>

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk