CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Jilael
Jilael

Reputation: 172

Excel SUMPRODUCT function using WEEKNUM

Hi I've got some rather large sheets detailing call status, revenue, etc that I can download directly from my software vendor. Relevant is this column

COMPLETED
1/1/2017 00:00
1/1/2017 02:30
etc

I am trying to get a count of this by weeknumber for graphical purposes so I created a seperate spreadsheet where I can filter multiple years of data and display in a graph.

I created a table such as:

WeekNumber | Count | Revenue

I'm attempting to use this formula for the Count but it uses the header cell which gives me #VALUE. If I could skip the header cell I believe it would work but every variation I try fails.

=SUMPRODUCT(--('2017Q1'!J:J>0)*(WEEKNUM('2017Q1'!J:J+0)=A3))

Upvotes: 1

Views: 600

Answers (1)

ian0411
ian0411

Reputation: 4265

WEEKNUM can not be used in an array. But you can achieve this with a custom function and here is the code:

Function WeekNum2(dates As Variant) As Variant()

    Dim weeks() As Variant
    ReDim weeks(dates.Cells.Count - 1, 0)
    Dim i As Long

    For i = 0 To dates.Cells.Count - 1
        weeks(i, 0) = Application.WorksheetFunction.WeekNum(dates.Cells(i + 1).Value)
    Next i

    WeekNum2 = weeks

End Function

Then in your formula, use WeekNum2 instead of WEEKNUM.

Also, please skip the header because that will mess up the formula since it only accepts numbers. Plus, it is not recommended to use the whole column as SUMPRODUCT will slow you down. Maybe use a range instead to make your life easier.

Upvotes: 1

Related Questions