Search code examples
excelexcel-formulaexcel-2007pivot-tablesumifs

Sum array of data within date range where date is text

I have an Excel table of the following format:

enter image description here

And I would like to answer the following question using Excel formulae:

  • What is the sum for Disney during 17-Nov until 20-Nov?

My attempts I tried the following approaches, unsuccessfully:

  1. Using SUMIFS with an array:

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

where i1 contains Disney, i2 contains 17-Nov in the date format, and i3 contains 20-Nov in the date format.

But this doesn't work because we are submitting an array where we must specify a range of cells. So I tried the following method:

  1. Using SUMIFS with a range:

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

But this doesn't work either, since I think we are using the >, < operators for text (the date values in the table).

So, what to do?
Should I change the format of the table completely?
Should I convert it back to range?

Solution

  • You can use Index and Match to select the column to sum.

    Something like 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 also take advantage of the Table Structured Addressing like this
    (tested in Excel 2010, so Table formats may be slighly different in Excel 2007)

    =SUMIFS(INDEX(MyTable,,MATCH(I1,MyTable[#Headers])),MyTable[Date],">="&I2,MyTable[Date],"<="&I3)