Solved: Sum Crazy, sum columns with variable headers

[arkusM]

Good day all.

I am redeveloping a tracking sheet for hedging program. Excel interfaces with Bloomberg data terminal for the live data feeds and I take it and massage the data into from reports. Access is not an option.

I have built a table that calculates the dollar position relative to the start month. If you look at the attachment my explanation may be more clear.

All dates are set to the first of the month and all calculations are full months only (nature of our business/transactions).

So on row 9 the start date is Nov 2005 so Column(D) is Nov 2005 and Column(E) is Dec 2005.
Whereas on Row 13 Column(D) is Jul 2009 and column (E) is Aug 2009.

I am trying to get the totals for each month, only for Company ABC, so the sum of the data for say Nov 2009 but I don't want to include XYZ. The Nov data resides in H13, D14, F15; but I don’t want to include F15.

I would normally use a sumproduct() formula but it that is only adequate for a single row, where as I need it for the whole table.

There is only a possiable 13 forward months (Month 12)

Presently this is calculated in realtime. Calculating every few minutes is an option... (which I will do, if I end up strong-arming a noob VBA proc.)

[mbarron]

Try this UDF

Function sumCrazy(myRange As Range, mMonth As Integer, Optional strCompany As String) As Double
Dim i As Long, j As Integer, fMonth As Integer, addEm As Boolean, m as Double

For i = 0 To myRange.Rows.Count - 1
fMonth = Month(Cells(myRange.Row + i, myRange.Column + 1))
If Cells(myRange.Row + i, myRange.Column) = strCompany Or strCompany = "" Then
addEm = True
Else
addEm = False
End If
For j = 0 To myRange.Columns.Count - 3
If addEm = True Then
If Month(DateAdd("m", j, Cells(myRange.Row + i, myRange.Column + 1))) = mMonth Then
m = m + Cells(myRange.Row + i, myRange.Column + j + 3)
End If
Else
m = m
End If
Next
Next
sumCrazy = m
End Function
To use the function:
=sumCrazy(range,month#,CompanyName)
where
range is all data you want to evaluate including the company names, Term Start and Term End columns
month # is the number of the month you want to display the result for
CompanyName is the name of the company your are evaluating - if you leave the company name blank, data for all companies are returned.

I've attached a demo using your attachement:

[arkusM]

wow... wow... It took me a few minutes to test to see if it was giving the correct results. Seems to work on the sample data...
I will see if I can adapt this to the working data.
THank you. I willl post back if I have trouble.
Also would I be correct to dim m as long?

Cheers. Mark

[mbarron]

Where'd "m" go??? I know I had it there before. Musta fallen off. It should be dimmed as double.

I've added the declaration to the original post.

[arkusM]

Ok.

I will have to play with this to see if I can get it to work on the data, I provided only a sample. EDIT:(DUH!!) LOL

Cheers

[arkusM]

Ok Got it to work on the data. Though I think I will have to put this into a module that is called from code as there are too many functions to be efficient. But the code is useful both for the problem at hand and for general learning - the DateAdd() is new to me. Thank you for your help.

Edit: to correct the funtion name!! LOL

[Aussiebear]

I think you meant DateAdd() rather than DataAdd()

[arkusM]

I think you meant DateAdd() rather than DataAdd()

Lol. DataAdd() must have been a fruedian slip. I wish it were possiable, I think I want an Excel Version that makes a model the way I imagine it in my head. Where is the "Just Do It" button. oh, I think Tiger Woods has taken it, he obviouly followed his sponsors slogan too closely. ;)