SUMIF or SUMIFS with Variable Column

[neilfleming]

I'm trying to SUMIF project staffing allocations over a fixed range of rows but with a variable column depending on the date in that column. I.e., I have a couple of years' worth of staffing data in columns, and a bunch of projects and employees in those rows.

I want to sumif for "Project A" the allocations in column "BQ".

It's a simple SUMIF statement:
=SUMIF('Project Staffing'!$A$5:$A$678,$B26,'Project Staffing'!BQ$5:BQ$678)

Here is the formula that returns precisely the result: 'Project Staffing'!BQ$5:BQ$678
="'Project Staffing'!"&SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('Project Staffing'!$N$4:$DM$4,0,MATCH($L$3-WEEKDAY($L$3,2)+1,'Project Staffing'!$N$4:$DM$4,0))),4),"1","")&"$5"&":"&SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('Project Staffing'!$N$4:$DM$4,0,MATCH($L$3-WEEKDAY($L$3,2)+1,'Project Staffing'!$N$4:$DM$4,0))),4),"1","")&"$678"

Where $L$3 is any date, like Today(), which I then convert to the starting Monday of that particular week.

When I substitute 'Project Staffing'!BQ$5:BQ$678 with the formula above in the SUMIF formula, Excel says there is a problem with the formula and won't even let me leave the formula bar. I can substitute the 'Project Staffing'!BQ$5:BQ$678 actual text into that formula and it works fine.

So, the problem is less the specifics of my formula, and more of how do I use the result as an argument in my SUMIF formula.

Suggestions?

Thanks...Neil

 

[neilfleming]

Darn! I looked at some of my other work and the answer is INDIRECT(). Geeze!