sumifs with sum range across 2 columns

[kerevenk]

I have tried several different formulas to get the results I need. The one that seems the most logical to me is sumifs but I keep getting a #value error. I do not understand why since all dates are formatted as dates and all numbers as numbers. I was hoping someone would have a solution to my problem.

Here is what I am trying to accomplish:
Prod workbook contains due dates and release dates and quantities. daily tracker sums up the qty available to release for each day and sums up the quantity released by its due date. If date released and second date are <= the date due then the sums of both qty released columns needs to entered in the amount released column on daily tracker.

I have included the formula I am currently trying to use in the examples below. I was unable to attach any actual files. Please offer any advice/solutions you have.

prod workbook
[TABLE="width: 500"]
<tbody>[TR]
[TD]date due[/TD]
[TD]date released[/TD]
[TD]second date released[/TD]
[TD]qty available[/TD]
[TD]qty released[/TD]
[TD]qty released[/TD]
[/TR]
[TR]
[TD]12/1/15[/TD]
[TD]12/1/15[/TD]
[TD]12/5/15[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]12/1/15[/TD]
[TD]12/1/15[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/1/15[/TD]
[TD]12/1/15[/TD]
[TD]12/4/15[/TD]
[TD]100[/TD]
[TD]25[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]12/2/15[/TD]
[TD]12/2/15[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/2/15[/TD]
[TD]12/5/15[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


daily tracker
[TABLE="width: 404"]
<colgroup><col span="3"><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]amount due[/TD]
[TD]amount released[/TD]
[/TR]
[TR]
[TD]12/1/15[/TD]
[TD]300[/TD]
[TD]=SUMIFS('[prod workbook.xlsx]Sheet1'!$E$2:$F$6,'[prod workbook.xlsx]Sheet1'!$A$2:$A$6,A2,'[prod workbook.xlsx]Sheet1'!$B$2:$B$6,"<='[prod workbook.xlsx]Sheet1'!$A$2:$A$6",'[prod workbook.xlsx]Sheet1'!$C$2:$C$6,"<='[prod workbook.xlsx]Sheet1'!$A$2:$A$6")[/TD]
[/TR]
[TR]
[TD]12/2/15[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AngelEG]

kerevenk,

can you give an example of what should be added on the amount released for 12/1/15 and 12/2/15 on your example?

the reason you are getting the VALUE# error is because you are using for criteria "<='[prod workbook.xlsx]Sheet1'!$A$2:$A$6" the " makes the whole thing a string, it's not really functioning as a range.

if you can be a little more explicit of what needs to be added under which conditions I can help.

cheers,

Angel

 

[bosco_yip]

Try,

1] Total amount due B2, formula copy down:

=SUMIF(Sheet1!$A$2:$A$6,A2,Sheet1!$D$2)

2] Total amount released C2, formula copy down :

=SUM(SUMIF(Sheet1!$A$2:$A$6,A2,INDIRECT({"Sheet1!E","Sheet1!F"}&2)))

Regards

 

[kerevenk]

kerevenk,

can you give an example of what should be added on the amount released for 12/1/15 and 12/2/15 on your example?

the reason you are getting the VALUE# error is because you are using for criteria "<='[prod workbook.xlsx]Sheet1'!$A$2:$A$6" the " makes the whole thing a string, it's not really functioning as a range.

if you can be a little more explicit of what needs to be added under which conditions I can help.

cheers,

Angel

If the date in daily tracker is 12/1/15 and the due date is 12/1/15 then any amount that was released on or before 12/1/15 needs to be added.

So in the example provided for 12/1/15 the amount released should add up to 175 and that sum should go in column C in the daily tracker.

I hope this makes it more clear what I am trying to do. also do you have a recommendation on how I can tell it to evaluate whether columns B and C in the prod workbook are <= column A and get results?