excel - Sum array of data within date range where date is text -

excel - Sum array of data within date range where date is text -

i have excel table of next format:

and reply next question using excel formulae:

what sum disney during 17-nov until 20-nov?

my attempts tried next approaches, unsuccessfully:

using sumifs array:

=sumifs(c4:g8,c3:g3,i1,b4:b8,">="&i2,b4:b8,"<="&i3)

where i1 contains disney, i2 contains 17-nov in date format, , i3 contains 20-nov in date format.

but doesn't work because submitting array must specify range of cells. tried next method:

using sumifs range:

=sumifs(c4:g8,b4:b8,">="&i2,b4:b8,"<="&i3)

but doesn't work either, since think using >, < operators text (the date values in table).

so, do? should alter format of table completely? should convert range?

you can utilize index , match select column sum.

something this

=sumifs(index($b$4:$g$8,,match(i1,$b$3:$g$3)),$b$4:$b$8,">="&i2,$b$4:$b$8,"<="&i3)

you can take advantage of table structured addressing this (tested in excel 2010, table formats may slighly different in excel 2007)

=sumifs(index(mytable,,match(i1,mytable[#headers])),mytable[date],">="&i2,mytable[date],"<="&i3)

excel excel-formula excel-2007 pivot-table sumifs