Sum specific rows in certain column of table

Hi,

Let’s say I have a column like the following:

I need to develop a formula that will sum all rows whose date in column A is greater than today for the column that I specify in a given cell.

I was able to get the following formula to work to sum up all rows in the correct column using SUMPRODUCT and HLOOKUP:

=SUMPRODUCT(HLOOKUP(G2,P1:U13,{2,3,4,5,6,7,8,9,10,11,12,13},TRUE)

Where G2 is the value used to pick the correct column and P1:U13 is the lookup table. But when tried to make the stuff in curly brackets dynamic by entering the following into a helper cell

=IF(B2<N2,”N,”,””)&IF(B2<N3,”3,”,””)&IF(B2<N4,”4,”,””)&IF(B2<N5,”5,”,””)&IF(B2<N6,”6,”,””)&IF(B2<N7,”7,”,””)&IF(B2<N8,”8,”,””)&IF(B2<N9,”9,”,””)&IF(B2<N10,”10,”,””)&IF(B2<N11,”11,”,””)&IF(B2<N12,”12,”,””)&IF(B2<N13,”13,”,””)

And then changing the static part of my original formula to “{“&LEFT(M19,LEN(M19)-1)&”}” but now I’m getting #REF! error.

Roy,

Thank you for your reply. Please see the stripped down workbook with only the pertinent information included for this question.

That works out extremely well for me! Thank you!! Also, I realize that I meant to put this thread under the formulas section so thank you also for answering it despite the fact that I put it in the wrong area. The only change that I made with what you sent was I removed the drop down box that you added for year selection since I want to be able to put it other options besides just the years in the first row of the table. I tested it out with entering numbers like 6, 9, 13, etc. and it still works the way I want it to. Thank you again for your help