Good Day,

I have a weighted average formula here:

=IFERROR(SUM(--IF(AND(B28>$D$6,B28<=$D$7),$D$17*$D$5,0),--IF(AND(B28>$F$6,B28<=$F$7),$F$17*$F$5,0),--IF(AND(B28>$H$6,B28<=$H$7),$H$17*$H$5,0),--IF(AND(B28>$J$6,B28<=$J$7),$J$17*$J$5,0),--IF(AND(B28>$L$6,B28<=$L$7),$L$17*$L$5,0))/SUM(--IF(AND(B28>$D$6,B28<=$D$7),$D$5,0),--IF(AND(B28>$F$6,B28<=$F$7),$F$5,0),--IF(AND(B28>$H$6,B28<=$H$7),$H$5,0),--IF(AND(B28>$J$6,B28<=$J$7),$J$5,0),--IF(AND(B28>$L$6,B28<=$L$7),$L$5,0)),0)

Where B28 to B51 is a time, i.e. 1:00 AM, column D-L are calling campaigns with varying hours of operations. Row 6 is start time and row 7 is end time. Row 5 is the volume, row 17 is the handle time. I am trying to calculate the average handle times for each hour of the day based on the campaigns which are running within their hours of operation. There is probably a more efficient way of doing it, but this is what I figured out and it works. My problem is I would like to make this formula able to add more columns to increase the number of campaigns. I had previously done this for another formula using COUNTA and OFFSET, but I don't think that will work here unless the formula is completely redone.

Any help/suggestions would be greatly appreciated. If I haven't explained clearly please let me know.

Thanks in advance.

Jeff