Sumproduct formula to trade the Week

What values do you have in 'EZE Comp P&L'!$Q$2:$Q$393?

OK then you need to think about what those 2 parts of the formula are giving you. If you have 3/24/14 in a cell, that is actually 41722 - the number of days elapsed since 1/1/1900, and in another cell, you have weeknum("date") that will give you 13.

Maybe consider adding a helper column to extract the weeknum from the date, then use that in the calc?

upload a sample workbook, im not going to guess

hmm seems weeknum() does not work that way the same as month() does. Let me ask the others

If testing a range of dates for WEEKNUM you have to coerce the range of dates into an array:

=SUMPRODUCT(--(WEEKNUM(A1:A10+0)>25))

This will not work in Excel versions 2003 and earlier.

OK so what am I missing here, Tony?

=(SUMPRODUCT((--WEEKNUM('EZE Comp P&L'!$Q$2:$Q$5000)=WEEKNUM(B$7))*('EZE Comp P&L'!$B$2:$B$5000="pHILLIPINES")*('EZE Comp P&L'!$O$2:$O$5000)))/1000000

Q contains real dates, B7 contains TODAY()

Assuming the formula is otherwise correct:

=(SUMPRODUCT(--(WEEKNUM('EZE Comp P&L'!$Q$2:$Q$5000+0)=WEEKNUM(B$7))*('EZE Comp P&L'!$B$2:$B$5000="pHILLIPINES")*('EZE Comp P&L'!$O$2:$O$5000)))/1000000

Eh, let's do it like this...

=SUMPRODUCT(--(WEEKNUM('EZE Comp P&L'!$Q$2:$Q$5000+0)=WEEKNUM(B$7)),--('EZE Comp P&L'!$B$2:$B$5000="pHILLIPINES"),'EZE Comp P&L'!$O$2:$O$5000)/1000000

That actually worked great Tony, thanks forthe assist...
=(SUMPRODUCT((--WEEKNUM('EZE Comp P&L'!$Q$2:$Q$5000+0)=WEEKNUM(B$7)-1)*('EZE Comp P&L'!$B$2:$B$5000=$A12)*('EZE Comp P&L'!$O$2:$O$5000)))/1000000

@ cartica, you have no data in week 13 (todays date), so I added the WEEKNUM(B$7)-1 to get some data to test - remove that for your data

------

Do you ever have data across more than one year? In that situation you might want to avoid using WEEKNUM because 1) you might confuse week n in one year with week n in other years and 2) WEEKNUM always starts on Jan 1st as 1, Dec 31st will be in a different week, as far as WEEKNUM is concerned, even if it's in the same actual week.

If you have a cell that contains the start date of the week it might be easier to use SUMIFS like this

=SUMIFS(Sum_range,Date_range,">="&Z2,Date_range,"<"&Z2+7)

where Z2 contains start date of the week - you can add more conditions to SUMIFS as required

Originally Posted by cartica

What does the ((-- in front of Weeknum do?, as well as the +0?

For the double unary minus, --, see this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

The +0...

In Excel versions 2003 and earlier, the WEEKNUM function is part of the Analysis ToolPak add-in. Functions in the ATP could not be used to calculate arrays. In Excel 2007 all the functions that were in the ATP from earlier versions were "rolled" into application as built-in functions. In other words, the ATP add-in was no longer needed to use some functions including the WEEKNUM function.

The old ATP functions can now calculate arrays BUT you have to force the array calculation. One way to do that is to add 0 to each numeric element of the array (range).