Pivot table Calculated field - Sumif(s) between two dates?

Question

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:

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:

'SUMIFS' or SUMPRODUCT is probably the way to go. But for comparing in SUMIFS, you will need to use ">="&Calendar and "<="&Calendar instead. — ian0411
Like this? = SUMPRODUCT(Units,Start,">= &Calendar",Finish,"<= &Calendar") - this resulted in #VALUE! in all cells = SUMPRODUCT(Units,Start,">= &Calendar",Finish,"<= &Calendar") - this resulted in formula error — samthaman
=SUMIFS(Units,Start,">="&Calendar,Finish,"<="&Calendar) or =SUMPRODUCT(Units,Start>=Calendar,Finish<=Calendar) if I didn't twist my eyes typing here. Or try and then post the screenshot here so we can better assist you. — ian0411
I'm still having the same issues after moving the quotes to the correct position. Not sure sure what you'd want to see in the screenshot, but I'm happy to take one. — samthaman

ian0411 ian0411 · Accepted Answer · 2017-10-05T13:28:49

From the data you have in the screenshots, this is what I came up.

The formula to use in column 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):

=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.

Pivot table Calculated field - Sumif(s) between two dates?

1 Answers