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.
 

C with no eyes

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

Forum statistics

Threads
1,082,117
Messages
5,363,256
Members
400,723
Latest member
Jsdk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top