+ Reply to Thread
Results 1 to 3 of 3

Using weeknum in a Sumproduct calculation

  1. #1
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Using weeknum in a Sumproduct calculation

    I can't seem to figure out how to get weeknum to work within sumproduct.

    In column A I have dates and based on a value in B I would like to sum C.

    Any thoughts on what I am missing here?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jeffreybrown; 05-01-2010 at 03:43 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Using weeknum in a Sumproduct calculation

    Hello Jeff,

    WEEKNUM is one of those functions that doesn't accept a range argument (like many Analysis ToolPak functions), e.g. this formula just returns #VALUE! error

    =WEEKNUM(A1:A10)

    You can calculate WEEKNUM without using WEEKNUM function......but in this case you don't actually need the week number. If you find the previous Monday to the date in question then you can compare that against the previous Monday for the range.

    You can find previous Monday to a date in B3 with

    =B3-WEEKDAY(B3,3)

    so then your formula could be this one in D2 copied down

    =IF(WEEKNUM($A1,2)=WEEKNUM($A2,2),"",SUMPRODUCT(--($A$2:$A$19-WEEKDAY($A$2:$A$19,3)=A2-WEEKDAY(A2,3)),--($B$2:$B$19=B$2),$C$2:$C$19))

    Note: although this works further down the column it gives an error in D2.....that's a separate issue, error is caused by trying to apply WEEKNUM function to the text value in A1. One way round that is to have a separate formula for D2, i.e. just the SUMPRODUCT, see attached

    =SUMPRODUCT(--($A$2:$A$19-WEEKDAY($A$2:$A$19,3)=A2-WEEKDAY(A2,3)),--($B$2:$B$19=B$2),$C$2:$C$19)

    Of course a simpler way might be to have a helper column with WEEKNUM as you had in column E.......
    Attached Files Attached Files

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Using weeknum in a Sumproduct calculation

    Thanks DLL...and especially for the great explanation. I probably would be scatching my head for many years to come to try and figure that one out.

    I think I will end up going for the helper column since this will eventually become a VBA routine. Just trying to work it out on "paper" before I hit the coding.

    I'm going to list the week numbers down column E, but I will conditional format the numbers inbetween as white font so I just get the first of the week and with that I can update the formula to...

    =IF(E1=E2,"",SUMPRODUCT(--($A$2:$A$21-WEEKDAY($A$2:$A$21,3)=A2-WEEKDAY(A2,3)),--($B$2:$B$21=B$2),$C$2:$C$21))

    Thanks again for the help...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1