Sum a table of columns & rows

[Frank Costa]

I have a spreadsheet of 154 Rows (all unique project numbers in numerical
order) and 9 columns of account numbers (some are similiar and some are user
entered, therefore there could be 'blanks' with no data in them).

I am trying to create a table that will only give me the project number if
there are dollars in one or more of the columns. This would be used for data
entry (and that is why I would like to have the columns summed up - to remove
duplicates).

Any ideas? I have given a brief example below:
F, G, & H
are User Defined Cols
A B C D E F G
H
1 Project # 1100 1111 1100 1100 "Blank" 4233 1111
2 00001 $50.00 $25.00 $3.00 $1.00 $25.00 $10.00
3 10000 $5.00 $
5.00 $10.00
4 25000 $50.00 $10.00 $1.00

What I would like to see is a table of the summarized data below:

Project Account Amount
00001 1100 $54.00
00001 1111 $35.00
00001 4233 $25.00
10000 1111 $15.00
10000 4233 $ 5.00
25000 1100 $50.00
25000 1111 $10.00
25000 4233 $ 1.00

Is this something that I can create using a pivot table and if so, how?

Thanks for your help!

 

[Don Guillett]

try using sumproduct idea
=sumproduct((a2:a200="0001")*(b2:b200=1100)*c2:c200)

 

[Frank Costa]

Don, thanks for your response, but it confuses me.

In my example, I do have some account numbers that will be the same, so I
can identify them. My issue comes in where there are five user defined
account numbers (which may be the same as the hard coded numbers). Those
user defined account numbers are entered into a column and my table picks
them up from a formula such as "=c4" (where c4 is the cell the user enters
the account number).

Does that help clarify my original question?

Thanks for your help. I do appreciate it.

 

[jlepack]

Frank,

Try this macro. If anyone else could tell me a way to do away with the
LETTERS, then I would be appreciative. I'm just getting back to Excel
after a few years without it.

Public Sub TotalsOfPivotTable()
Dim wsin As Worksheet, wsout As Worksheet
Dim iProjLoop As Integer, iAccoLoop As Integer, x As Integer
Dim dValue As Double
Dim proj As String, acco As String
Dim bFound As Boolean

Const LETTERS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Set wsin = Sheets("yourSheetNameHere")
Set wsout = Sheets.Add
With wsout
.Name = "Output"
.Range("A1").Value = "Project"
.Range("B1").Value = "Account"
.Range("C1").Value = "Amount"
End With

Application.ScreenUpdating = False
wsin.Select
For iProjLoop = 2 To ActiveSheet.UsedRange.Rows.Count
proj = Range("A" & iProjLoop).Value
For iAccoLoop = 2 To ActiveSheet.UsedRange.Columns.Count
acco = Range(Mid(LETTERS, iAccoLoop, 1) & "1").Value
If Not acco = "" Then
dValue = Range(Mid(LETTERS, iAccoLoop, 1) &
iProjLoop).Value
If Not dValue = 0 Then
wsout.Select
bFound = False
For x = 2 To ActiveSheet.UsedRange.Rows.Count
If Range("A" & x).Value = proj And Range("B" &
x).Value = acco Then
Range("C" & x).Value = Range("C" & x).Value
+ dValue
bFound = True
End If
Next x
If Not bFound Then
x = ActiveSheet.UsedRange.Rows.Count + 1
Range("A" & x).Value = proj
Range("B" & x).Value = acco
Range("C" & x).Value = dValue
End If
wsin.Select
End If
End If
Next iAccoLoop
Next iProjLoop
Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

Then just change to c4
sumproduct((a2:a200="0001")*(b2:b200=c4)*c2:c200)