CODEWITHSUNDEEP
excelexcel-formulaexcel-2007pivot-tablesumifs
info_seekeR
info_seekeR

Reputation: 1326

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:

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?

Upvotes: 2

Views: 1770

Answers (3)

pnuts
pnuts

Reputation: 59485

For greater flexibility I would consider 'flattening' the data (eg) and then using the PivotTable filtering facilities.

Upvotes: 1

teylyn
teylyn

Reputation: 35970

This is what Sumproduct() is good for.

=SUMPRODUCT($C$4:$G$8*($C$3:$G$3=company)*($B$4:$B$8>=startDate)*($B$4:$B$8<=finishDate))

enter image description here

company, startDate and finishDate are named cells for A11, B11 and C11. Dates in the table and in row 11 are real dates.

If you store the date as text values, you won't be able to calculate data in a date range. The dates need to be real dates. Text cannot be used to define date ranges.

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53136

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)

Upvotes: 1

Related Questions