Use of Weeknum in SUMPRODUCT

[Anthony47]

Hello everyone.
I tryed using Weeknum as one of SUMPRODUCT matrixes to obtain the weekly sum of a value.
Basically the formula is

=SUMPRODUCT((WEEKNUM(A2:A100,1)=K1)*1,B2:B100)

A2:A100 contain the date and B2:B100 contain the value i need to sum up; K1 is the weeknumber whose values I am trying to sum.
But I get a #VALUE error, referred to matrix 1.
If I use Weeknum in a standard fashion (eg =WEEKNUM(TODAY())) it is of course Ok.

When I use a similar formula for monthly sums it is ok:

=SUMPRODUCT((MONTHS(A2:A100)=J1)*1,B2:B100)

Does it means that WEEKNUM cannot be used in a matrix formula or am I using it in the wrong sintax?

Thank you.

 

[Lewiy]

I believe that it is simply that you cannot use WEEKNUM within SUMPRODUCT because it is an add-in function rather than a naitive Excel function. You should be able to get around this by using a helper column with the week numbers in.

 

[Aladin Akyurek]

WEEKNUM() cannot return an array of values, just a scalar. That's why it cannot be invoked from within a SumProduct or kindred formulas.

 

[barry houdini]

Probably simplest to use a helper column as Lewiy suggests but as this formula is equivalent to WEEKNUM(A1,1)

=INT((A1-WEEKDAY(A1)-DATE(YEAR(A1),1,1))/7)+2

You could use that approach within SUMPRODUCT, i.e. for your example

=SUMPRODUCT((INT((A2:A100-WEEKDAY(A2:A100)-DATE(YEAR(A2:A100),1,1))/7)+2=K1)*1,B2:B100)

 

[Anthony47]

Thank you for all the explanations and suggestions Also, BH's alternate formula is working great, so I have the choice between the helper column (that would give me the additional benefit of the autofilter) and this formula.

Thanks again.

 

[Slawek]

Hi, i tried to copy the exact formula as Barry shown above but I wanted to replace the cell ranges from different file or even just sheet in same file but I either cannot finish the formula (error pops up) or I get a #Value error... ?

=SUMPRODUCT((INT(Shee2!T1:T100-WEEKDAY(Sheet2!T1:T100)-DATE(YEAR(Sheet2!T1:T100),1,1))/7)+2=A4)*1,Sheet2!V1:V100)

Your help would be much appreciated.

 

[Gilliam]

Hi, i tried to copy the exact formula as Barry shown above but I wanted to replace the cell ranges from different file or even just sheet in same file but I either cannot finish the formula (error pops up) or I get a #Value error... ?

=SUMPRODUCT((INT(Sheet2!T1:T100-WEEKDAY(Sheet2!T1:T100)-DATE(YEAR(Sheet2!T1:T100),1,1))/7)+2=A4)*1,Sheet2!V1:V100)

Your help would be much appreciated.

for starters, you missed a "t" however i think that produces a #ref! error

 

[Slawek]

Hi, thanks for checking! But that's not the problem here... (I typed "Sheet" manually just here go translate it etc.)

 

[barry houdini]

=SUMPRODUCT((INT(Shee2!T1:T100-WEEKDAY(Sheet2!T1:T100)-DATE(YEAR(Sheet2!T1:T100),1,1))/7)+2=A4)*1,Sheet2!V1:V100)

#VALUE! error could be caused by any text in Sheet2!T$1:T$100 - do you have headers in row 1? If so just start from row 2 like this:

=SUMPRODUCT((INT((Sheet2!T$2:T$100-WEEKDAY(Sheet2!T$2:T$100)-DATE(YEAR(Sheet2!T$2:T$100),1,1))/7)+2=A4)*1,Sheet2!V$2:V$100)

.....although in Excel 2007 or later excel versions you can use WEEKNUM here, like this:

=SUMPRODUCT((WEEKNUM(Sheet2!T$2:T$100+0)=A4)*1,Sheet2!V$2:V$100)

 

[Slawek]

Hi Barry,

Thanks a lot, you were right! the text in headers was responsible for this error.

But now I have to raise the bar a little.
As before, I need to sum values in column V for selected week numbers in T specified in =A4. But also now I need to add a condition that the sum is done only for a specific reference/material number in column C specified in =A1. The reference in A1 is a numeric ref "083015532".


=SUMPRODUCT((INT((Sheet2!T$2:T$100-WEEKDAY(Sheet2!T$2:T$100)-DATE(YEAR(Sheet2!T$2:T$100),1,1))/7)+2=A4)*1,--(Sheet2!$C$2:$C$100=A1),Sheet2!V$2:V$100)


But again Value! ... any hope for me here? would appreciate your help !!!! and next time you are in Amsterdam beer (and the rest...) is on me!