Variable range SUMPRODUCT for weighted AHT

[sirgalahad71]

Greetings,
I am trying to get a weighted AHT for a range of data that will vary based on the date range.
Normally to get a weighted AHT you would use the formula: =sumproduct(Vol Range, AHT Range)/Vol Total.

This is my most recent attempt:
'=SUMIFS(C$2:C$100*D$2:D$100,$B$2:$B$100,">="&Text($B$2,"m/d/yyyy"),$B$2:$B$100,"<="&text($B120+6,"m/d/yyyy"))/C120

$B$2:$B$100 = 1/1/2016 to 4/9/2016+
$C$2:$C$100 = The call volume by day.
$D$2:$D$100 = AHT volume by day.
$B$120 = Variable end date.
$C$120 = Total of volume range.
Formula: '=SUMIFS(C$2:C$100,$B$2:$B$100,">="&$B$2,$B$2:$B$100,"<="&$B120)

I have also tried:
'=SUM(SUMIFS(C$2:C$100,$B$2:$B$100,">="&TEXT($B$2,"m/d/yyyy"),$B$2:$B$100,"<="&TEXT($B120,"m/d/yyyy")),SUMIFS(D$2:D$100,$B$2:$B$100,">="&TEXT($B$2,"m/d/yyyy"),$B$2:$B$100,"<="&TEXT($B120,"m/d/yyyy")))/C120

Any assistance would be greatly appreciated.

Thanks

SirGalahad71

 

[StephenCrump]

Welcome to the Forum!

It's not clear what you're trying to do here, e.g. presumably you're only wanting to include some dates, but it looks like C120 includes all dates? And also why you're converting dates to text format?

Can you perhaps post a simplified example to illustrate the data you've got, and the result you're looking for?

 

[sirgalahad71]

I want to be able to select a range of dates from $B$2 to wherever. Could be 3 days. Could be a week. Could be a month.
The result should be calculated without having to provide the specific range each time. just change the end date and the formula adjusts.
C120 is the sum of the volume based on the start date $B$2 and the end date in $B$120. $B$120 is the end date which is then used as part of the formula that calculates the result. I converted the dates to text because when I was viewing the formula with fx excel was converting the dates to numbers and I thought I had to dictate the format I wanted to see the dates in. Perhaps that is unnecessary?

Couldn't figure out the process to paste a picture so the below will have to suffice until I do.
The day's are in column A, Dates in B, Vol in C and AHT in D. The column headings are in row 1.

Day Date Vol AHT
Fri 1/1/2016 54 613
Sat 1/2/2016 69 780
Sun 1/3/2016 52 687
Mon 1/4/2016 248 650
Tue 1/5/2016 179 643
Wed 1/6/2016 158 648
Thu 1/7/2016 138 700
Fri 1/8/2016 54 613

Thu 1/7/2016 898 629

In the example above 898, in C11, is the sum total of the Vol column based on the range where the first date is 1/1/2016 and the last date is 1/7/2016, in B11. The formula: =SUMPRODUCT(C2:C8,D2:D8)/C11 This gives the result 629 in D11.
I'm trying to come up with a formula whereby I can select the date range and can get the weighted AHT result. Eg 629.
Since this is not possible with SUMPRODUCT I have been trying other formula's without success.

 

[StephenCrump]

Couldn't figure out the process to paste a picture

It's better to post a screenshot. See Part B here, for how to do this: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

For your weighted average, try:

B13: ​=SUMPRODUCT(C2:C9,D2:D9,--(B2:B9>=StartDate),--(B2:B9<=EndDate))/SUMIFS(C2:C9,B2:B9,">="&StartDate,B2:B9,"<="&EndDate)

Excel 2010

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Day[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Vol[/TD]
[TD="align: center"]AHT[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]1 Jan 16[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]613[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Sat[/TD]
[TD="align: center"]2 Jan 16[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"]780[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]Sun[/TD]
[TD="align: center"]3 Jan 16[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]687[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Mon[/TD]
[TD="align: center"]4 Jan 16[/TD]
[TD="align: center"]248[/TD]
[TD="align: center"]650[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Tue[/TD]
[TD="align: center"]5 Jan 16[/TD]
[TD="align: center"]179[/TD]
[TD="align: center"]643[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Wed[/TD]
[TD="align: center"]6 Jan 16[/TD]
[TD="align: center"]158[/TD]
[TD="align: center"]648[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]Thu[/TD]
[TD="align: center"]7 Jan 16[/TD]
[TD="align: center"]138[/TD]
[TD="align: center"]700[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]Fri[/TD]
[TD="align: center"]8 Jan 16[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]613[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]StartDate[/TD]
[TD="align: center"]2 Jan 16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]EndDate[/TD]
[TD="align: center"]7 Jan 16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]Volume weighted AHT[/TD]
[TD="align: center"]669.2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

​

 

[sirgalahad71]

Perfect!
Thank you! This works exactly as I need it too. YAY!!!
This solves so many problems I have been trying to overcome and have had to work around.

Have a great day!

 

[StephenCrump]

Have a great day!

Thanks for posting back, and I will. It's a beautiful summer day here, and I'm heading off for a bike ride.