I'm attempting to calculate periods of out of stock for a fleet of rental equipment that has been in service for the past few years. I'm having trouble creating a sumif calculated field that sums units by date if date is between start and finish. My data looks like this:
我试图计算过去几年一直在使用的租赁设备车队的缺货期。我无法创建一个sumif计算字段，如果日期在开始和结束之间，则按日期对单位求和。我的数据如下所示：
Calendar |Start |Finish |Product |Units
2015-12-06|2015-12-6|2015-12-6 |Snowshoes |2
2015-12-07|2015-12-6|2015-12-7 |Snowshoes |1
Calendar - is a helper column I've added. It's sequential dates from launch to the present Start - is the start Date of a rental booking Finish - end date of the rental booking Product - What's being rented Units - How many are rented for that booking
日历 - 是我添加的帮助列。这是从发布到现在开始的连续日期 - 是租赁预订的开始日期结束 - 租赁预订的结束日期产品 - 租用的单位单位 - 预订租用了多少
I'd like the pivot table to look like:
我希望数据透视表看起来像：
Date | Snowshoes | Tent ... etc
2015-12-06 | 3 |
2015-12-07 | 1 |
I'm having a hard time setting up calculated field that will sum units if date is between start and finish, I keep getting formula errors.
我很难设置计算字段，如果日期在开始和结束之间，则会对单位求和，我不断得到公式错误。
Here's the formula I'm attempting to use to create a calculated field:
这是我试图用来创建计算字段的公式：
= sumifs( Units ,Start,">= Calendar" , Finish,"<= Calendar")
Is this even the best way to go about solving this problem? Is my formula the issue or is the entire approach flawed?
这甚至是解决这个问题的最好方法吗？我的公式是问题还是整个方法都有缺陷？
Adding screenshots: 
添加屏幕截图：
1 个解决方案
#1
0
From the data you have in the screenshots, this is what I came up.
根据屏幕截图中的数据，这就是我提出的。
The formula to use in column G:
G栏中使用的公式：
=SUMIFS($E$2:$E$29,$A$2:$A$29,"<="&F2,$B$2:$B$29,">="&$F2)
The formula to use in column H (BTW, this is just for your reference. You can use either one of them):
在H列中使用的公式（BTW，这仅供您参考。您可以使用其中任何一个）：
=SUMPRODUCT(--($A$2:$A$29<=F2),--($B$2:$B$29>=F2),$E$2:$E$29)
From here, I created a Pivot Table like this:
从这里开始，我创建了一个像这样的数据透视表：
Hopefully this can help you. But definitely let me know if I miss anything from your question.
希望这可以帮助你。但是如果我错过了你的问题，请务必告诉我。
#1
0
From the data you have in the screenshots, this is what I came up.
根据屏幕截图中的数据，这就是我提出的。
The formula to use in column G:
G栏中使用的公式：
=SUMIFS($E$2:$E$29,$A$2:$A$29,"<="&F2,$B$2:$B$29,">="&$F2)
The formula to use in column H (BTW, this is just for your reference. You can use either one of them):
在H列中使用的公式（BTW，这仅供您参考。您可以使用其中任何一个）：
=SUMPRODUCT(--($A$2:$A$29<=F2),--($B$2:$B$29>=F2),$E$2:$E$29)
From here, I created a Pivot Table like this:
从这里开始，我创建了一个像这样的数据透视表：
Hopefully this can help you. But definitely let me know if I miss anything from your question.
希望这可以帮助你。但是如果我错过了你的问题，请务必告诉我。