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.
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.
Last edited by jeffreybrown; 05-01-2010 at 03:43 PM.
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.......
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...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks