ExcelBanter - sum matrix cells based on value in a separate cell

Hi,
Give this a try:

Sub TotalProjectTimes()

Dim lastrow As Long, r As Long, c As Integer, idx As Integer
Dim v() As Variant, ProjSum() As Double
Dim res
Dim ws1 As Worksheet

Set ws1 = Worksheets("sheet1")

ReDim v(1 To 1)
n = 0

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row

For r = 2 To lastrow

For c = 3 To 15 Step 2 ' Loop through project IDs for each day
(Sunday to Saturday)
If .Cells(r, c) < "" Then
res = Application.Match(.Cells(r, c), v, 0) 'Match against
stored project IDs
If IsError(res) Then ' new project ID
' redimension arrays ....
n = n + 1
ReDim Preserve ProjSum(1 To n)
ReDim Preserve v(1 To n)
v(n) = .Cells(r, c) ' Store project ID
idx = n
Else
idx = res
End If
ProjSum(idx) = ProjSum(idx) + .Cells(r, c - 1) ' Summ times
for this project
End If
Next c

Next r

End With

' List Project Ids and associated times
For i = 1 To UBound(v)
Debug.Print v(i), ProjSum(i)
Next i

End Sub

"excelman" wrote:

I have a project table to record time worked on projects each day
DayOfWeek: contains the number of hours worked on SUN, MON ...
SET: contains the Project Id

NAME | SUN | SET | MON | SET ...
--------|-------|------|-------|-------
John | 8 | set1 | 8 | set2
Jane | 10 | set3 | 8 | set2

Is it possible In VBA (or Excel spreadsheet) to produce this result
SET | TOTAL
------|---------
set1 | 8
set2 | 16
set3 | 10
...