Excel 2013 - Sumifs - Add all cells in a column

Question

I have the following formula in my Excel 2013 spreadsheet:

=SUMIFS(Employees!$T$2:$T$1114, Employees!$L$2:$L$1114, $A5, Employees!$J$2:$J$1114, 1, Employees!$Q$2:$Q$1114, "N", Employees!$K$2:$K$1114, C$3)

It is summing column T based on certain criteria. Looking at Employees!$T$2:$T$1114 as an example, I have a header row, so I start at T2. I then select all the cells in that column. which currently ends at 1114. The problem is, that number can change.

If I want to remove the hard-coding and just select all the cells that contain data in that column, then sum that data based on this criteria, how would I change this?

Answer 1

If the header above T isn't sumable, and/or the header above L isn't a valid criteria, then it won't matter if they are included in the sum_range and criteria_range, respectively. In that case, you can specify entire columns, like Employees!$T:$T, and not worry about where the data starts or ends.

=SUMIFS($A:$A, $B:$B, G2)

If there is data above the desired part of T, you could add another criteria to avoid it, for instance, by using an additional (hidden) column with markers.