CODEWITHSUNDEEP
excelintervals
vba_interested
vba_interested

Reputation: 15

how to sum with an interval

I am looking for a formula or macro which can employ the following: I need to sum-square the amount per week. This should be done from the beginning on. My data structure is as follow:

Col A   Col B   Column C
Year    Week    Amount
2000    1       368
2000    2       8646
 …      …       …
2000    52      46846
2001    1       656
2001    2       846
 …      …       …
2001    52      4651
2002    1       489
 …      …       …
2014    52      46546

I would have a column D in which I have the sum-squared of the amount per week. So Cell(Column "D", "week 2000w1") should be,

=SUMSQ(Amount 2000w1)

For the first year, this is easy. The problem occurs in the next year. In Cell (Column "D", week "2001w1") the formula should be,

=SUMSQ(Amount 2000w1;Amount 2001w1)

For the last year, cell (Column "D", week "2014w1") should be the formula,

=SUMSQ(Amount 2000w1;Amount 2001w1; Amount 2002w1;Amount 2003w1;Amount 2004w1; Amount 2005w1;Amount 2006w1;Amount 2007w1; Amount 2008w1;Amount 2009w1;Amount 2010w1; Amount 2011w1;Amount 2012w1;Amount 2013w1)

This should be done for the weeks 1 till 52 for all the years. Is there a quick way to do this?

Upvotes: 0

Views: 1774

Answers (2)

user3819867
user3819867

Reputation: 1120

Thank you Juhász Máté for stealing my jerb. Now I feel dumb for having this developed.
Anyways, here's a VBA solution.
For your sample dataset =SUMQ($C:$C, $B:$B, $B2) would give 804,881 i.e. the sum of *week1*s squares.
The advanced use as =SUMQ($C:$C, $B:$B, $B2, $A:$A, "<=", $A2) will give 135,424 that is sum of *week1*s for years lower than or equal 2000.

Public Function SUMQ(NumsToSquare As Range, Filter1 As Range, FilterCriterion As Variant, _
                     Optional Filter2 As Range, Optional FilterRelation As String, Optional FilterCriterion2 As Variant) As Long

Set NumsToSquare = Intersect(NumsToSquare, NumsToSquare.Worksheet.UsedRange)
Set Filter1 = Intersect(Filter1, Filter1.Worksheet.UsedRange)
RowsCount = Filter1.Rows.Count
ColumnsCount = Filter1.Columns.Count

If Not Filter2 Is Nothing Then Advanced = True
If Advanced Then Set Filter2 = Intersect(Filter2, Filter2.Worksheet.UsedRange)

On Error Resume Next
For i = 1 To RowsCount
    For j = 1 To ColumnsCount
        If Not Advanced Then
            If Filter1(i, j).Value2 = FilterCriterion Then SUMQ = SUMQ + NumsToSquare(i, j).Value2 ^ 2
        Else
            If Filter1(i, j).Value2 = FilterCriterion And Judge(Filter2(i, j).Value2, FilterRelation, FilterCriterion2) Then SUMQ = SUMQ + NumsToSquare(i, j).Value2 ^ 2
        End If
    Next j
Next i
End Function

Private Function Judge(var1 As Variant, FilterRelation As String, var2 As Variant) As Boolean
Judge = False
On Error GoTo err:
Select Case FilterRelation 'cf. https://msdn.microsoft.com/en-us/library/aa711633(v=vs.71).aspx
    Case "=" 'The = operator tests whether the two operands are equal.
        Judge = (var1 = var2)
    Case "<>" 'The <> operator tests whether the two operands are not equal.
        Judge = (var1 <> var2)
    Case "<" 'The < operator tests whether the first operand is less than the second operand.
        Judge = (var1 < var2)
    Case ">" 'The > operator tests whether the first operand is greater than the second operand.
        Judge = (var1 > var2)
    Case "<=" 'The <= operator tests whether the first operand is less than or equal to the second operand.
        Judge = (var1 <= var2)
    Case ">=" 'The >= operator tests whether the first operand is greater than or equal to the second operand.
        Judge = (var1 >= var2)
End Select
err:
End Function

Upvotes: 1

M&#225;t&#233; Juh&#225;sz
M&#225;t&#233; Juh&#225;sz

Reputation: 2327

This is a solution with worksheetfunction, you can develop similar with a macro too, but I think now it's easier without it:

=SUMSQ(INDEX([Amount]*([Week]=[@Week])*([Year]<=[@Year]),0))

enter image description here

Upvotes: 1

Related Questions