Sum specific rows in certain column of table

• Hi,

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

 date 1 3 5 10 15 20 1/1/21 11 13 13 15 15 17 2/5/21 12 12 14 14 16 18 3/5/21 11 13 13 15 15 17 4/2/21 11 13 13 15 15 17 5/7/21 12 12 14 14 16 18 6/4/21 11 13 13 15 15 17 7/2/21 11 13 13 15 15 17 8/6/21 12 12 14 14 16 18 9/3/21 11 13 13 15 15 17 10/1/21 11 13 13 15 15 17 11/5/21 12 12 14 14 16 18 12/3/21 11 13 13 15 15 17

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.

• Test.xlsx