# 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.

## Files

• \this is how I would do it. Look at the Yellow cells.

Each yellow cell in the table uses SUMIF to calculate the total.

The required amount is found using HLOOKUP and the value in G2.

Data Validation in G2 endures only the correct values can be picked.

• 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

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!