0

I am struggling with Excel here. I try to create a table with multiple sheets for all the storage units (in this example 19N) that we have. In every unit someone should be able to fill in a start date , a due date and a capacity need (picture1). Later, I want to have on sheet as an overview where Excel summarizes the capacity for every storage unit by date (picture2). I was able to summarize the number of events (like a count value) but would like to have the actual sum of the capacity column for every date. I tried:

=SUMIF(("'"&B$1&"'!$D$6:$D$60";((INDIRECT("'"&B$1&"'!$E$6:$E$60")<=$A5)*(INDIRECT("'"&B$1&"'!$F$6:$F$60")>=$A5))))

but just got an error.

Jerry
  • 70,495
  • 13
  • 100
  • 144
user2386786
  • 725
  • 9
  • 25
  • picture1 and picture2, if they are meant to be links, they do not work. What do you mean by "the actual sum of the capacity column for every date"? StartDate or DueDate? – zx8754 Jun 20 '13 at 12:47
  • There is no picture 2 in this thread. Do you want a sum of the capacity column GROUPED by date? As in, you sum the values of capacity column only if they occur on the same date? – Parseltongue Jun 20 '13 at 13:52
  • Do you have separate Excel files for each unit, or one Excel file with sheets for each unit? – zx8754 Jun 20 '13 at 14:12

1 Answers1

0

You want to use SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Also you forgot to use Indirect when referencing your sum range in the begining of your formula.

Use this formula below, if cell B1 contains the sheet name and cell A5 contains the Date to compare.

=SUMIFS( INDIRECT( B$1&"!$D$6:$D$60" ) , INDIRECT( B$1&"!$E$6:$E$60" ) ,"<="&$A5 , INDIRECT( B$1&"!$F$6:$F$60" ) , ">="&$A5 )

I hope this helps.

Scheballs
  • 532
  • 3
  • 14
  • Thanks for pointing me in this direction. This was what I was looking for. I now have the sheet the way I wanted it. – user2386786 Jun 24 '13 at 17:34