I have a dataset which has dates in row 1. when the range is converted into a Table, row 1 becomes my table headers and excel treats all column headers as Text regardless of the previous data type.

I have the following formula which when the dataset is stored as a range, and row 1 is stored as actual dates works to provide me a sum of all values in row 2 which fall between Jan 1 and Dec 1 2018.

=SUMIFS($AG2:$CZ2,$AG$1:$CZ$1,">="&DATE(2018,1,1),$AG$1:$CZ$1,"<="&DATE(2018,12,1))

However when the dataset is converted to a Table the result is 0 because the dates are now Text instead of the underlying date. i have tried changing the DATE(2018,1,1) to "1/1/2018" and datevalue("1/1/2018") however neither of these work.

I know that if i use Value(AG2) i will get back the underlying date in excel format, however i cannot figure out how to apply the Value to the full range, neither Value(AG2:CZ2) nor Value(AG2):Value(CZ2) seem to work.

i have seen a solution using sumproduct, however i would very much like to find a solution which is not executed as an array since this formula is in hundreds of thousands of cells across multiple columns (column each for 2014-2020).

The data needs to stay as a table (unfortunately) so that i can work with it in Power Query.

any insight or help is very much appreciated.