Sumif

[Hoosier03]

I am using Excel 2007. This is the equation that I have.

=SUMIF('Act Query'!$B22:$B261,C5,'Act Query'!DJ22:DJ436)

The equation works, but I have a problem. 'Act Query'!DJ22:DJ436 references a worksheet of data that is pulled from an SAP file. The problem is that this file is counting weeks and every few years there are 53 fiscal weeks in the year. Is there a way I can write this equation that it references the week number instead of a specific column?

Thanks,

Rodney

 

[c_m_s_jr]

=WEEKNUM(TODAY())

This will return the week number for today's date, but the week number is based on a calendar year. What is your fiscal calendar? Is it 12 months, just starting in a different month than January or is it a 4-4-5? 13 periods, etc?

 

[Hoosier03]

The fiscal calendar runs October through September.

 

[c_m_s_jr]

The following formula will calculate the weeknumber for the date in cell A1 based on a starting date of 10/1. I assumed your weeks run Sunday - Saturday. Additionally, you will have to change the 2011 in the formula everytime the year changes.


=ROUNDUP((A1-(IF(WEEKDAY(DATE(2011,10,1),1)=1,DATE(2011,10,1),DATE(2011,10,1)-(WEEKDAY(DATE(2011,10,1),1)-1)))+1)/7,0).

When tested it shows that the first week only has 1 day since 10/1/2011 starts on a Saturday.

 

[Hoosier03]

Thanks.