Using SumIF on Dates Stored as Text in Column Headers

[neb255]

Using Excel 2016
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.

 

[C with no eyes]

Could you put a hidden row above or below your table with Value(DataTableDate) then use your formula to calc from the column? (Haven't tested it, just thinking out loud)

 

[Aladin Akyurek]

Try...

=SUMPRODUCT($AG2:$CZ2,--($AG$1:$CZ$1+0>=DATE(2018,1,1)),--($AG$1:$CZ$1+0<=DATE(2018,12,1)))​

 

[neb255]

Hi Aladin, This worked perfectly. Thanks allot! Can you please explain what the "--" does here, and why we are adding 0 to the reference range?

 

[neb255]

Could you put a hidden row above or below your table with Value(DataTableDate) then use your formula to calc from the column? (Haven't tested it, just thinking out loud)

Hi C, This would work, i preferred not to add hidden rows though. Thanks for your suggestion

 

[Aladin Akyurek]

Hi Aladin, This worked perfectly. Thanks allot!

You are welcome.

Can you please explain what the "--" does here, and why we are adding 0 to the reference range?

SUMPRODUCT requires numbers. The -- and +0 are applied to coerce TRUE/FALSE values or text numbers into numbers.

In

--($AG$1:$CZ$1+0>=DATE(2018,1,1))

a 0 added to $AG$1:$CZ$1 makes the text dates in this range true dates i.e. numbers, while -- converts TRUE and FALSE values,
which come about by the >= test, to

1 and 0 values. Note that TRUE = 1 and FALSE = 0 in Excel.​

​

 

[neb255]

SUMPRODUCT requires numbers. The -- and +0 are applied to coerce TRUE/FALSE values or text numbers into numbers.

In

--($AG$1:$CZ$1+0>=DATE(2018,1,1))

a 0 added to $AG$1:$CZ$1 makes the text dates in this range true dates i.e. numbers, while -- converts TRUE and FALSE values,
which come about by the >= test, to

1 and 0 values. Note that TRUE = 1 and FALSE = 0 in Excel.​

​

Makes sense, thanks again

 

[Aladin Akyurek]

Makes sense, thanks again

Glad to help.