Using SumIF on Dates Stored as Text in Column Headers

neb255

Board Regular
Joined
Sep 14, 2011
Messages
60
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

C with no eyes

Board Regular
Joined
Nov 26, 2016
Messages
151
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Try...

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

neb255

Board Regular
Joined
Sep 14, 2011
Messages
60
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

Board Regular
Joined
Sep 14, 2011
Messages
60

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Board Regular
Joined
Sep 14, 2011
Messages
60
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,605
Members
412,330
Latest member
carlosjw
Top