Very Difficult Sumproduct formula

**amartino44** · 05-22-2013, 12:08 PM

Hello. I inherited a spreadsheet that is very complex. It has a section with the dollar cost of invoices for a specific trade by month. Then it has a section that calculations the average by quarter (Q1, Q2, etc.) I can't copy the spreadhseet because it is confidential but let me see if I can explain the spreadsheet and hopefully someone can explain what the formula is doing (step by step)

=((SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,1,4,7,10))*($D$7:$BL$7=BR$7)*$D214:$BL214)*SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,1,4,7,10))*($D$7:$BL$7=BR$7)*$D83:$BL83))+(SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,2,5,8,11))*($D$7:$BL$7=BR$7)*$D214:$BL214)*SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,2,5,8,11))*($D$7:$BL$7=BR$7)*$D83:$BL83))+(SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,3,6,9,12))*($D$7:$BL$7=BR$7)*$D214:$BL214)*SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$6:$BL$6=CHOOSE(BR$5,3,6,9,12))*($D$7:$BL$7=BR$7)*$D83:$BL83)))/SUMIFS($D83:$BL83,$D$5:$BL$5,BR$5,$D$7:$BL$7,BR$7)

D5:BL5 identifies the quarter for each month of data (e.g Jan= Q1). BR5 is Q1. D6:BL6 has the name of the months. I'm not sure what that "choose" formula is doing. D7:BL7 identifies the year for each month of data. BR7 is 2011. Row 214 has the severity numbers (the cost). Row 83 has the number of invoices (I guess to do a weighted average of the severity for the quarter). Those are all the variables. Hopefully someone can help.

I'm also trying to figure out why some of the quarter numbers are showing up as Div/0 when there is only recent information. If anyone can think of an easier formula to sumif for Q1 and year, and then do a weighted average of cost, I'm all ears.

**Pauleyb** · 05-22-2013, 02:52 PM

I recommend you use the 'Evaluate Formula' tool in Excel to see how this function works. Note, that since it is using some fairly long ranges, I would also recommend you reduce it. In other words, change all of the BL references to H (ex. $D$5:$BL$5 to $D$5:$H$5). This should allow you to see how the function works at a smaller scale; otherwise, you will see too many TRUE/FALSE results to keep track of. You can google 'sumproduct multiple criteria' (or something like that ) to understand how this works. Here is one link: http://www.ozgrid.com/News/apr-2005.htm#ExcelTips

BTW, the CHOOSE is just determining the quarter. The 1, 4, 7, and 10 are the month starts of each quarter.

**daddylonglegs** · 05-22-2013, 03:42 PM

Originally Posted by amartino44

D5:BL5 identifies the quarter for each month of data (e.g Jan= Q1). BR5 is Q1. D6:BL6 has the name of the months. I'm not sure what that "choose" formula is doing. D7:BL7 identifies the year for each month of data. BR7 is 2011. Row 214 has the severity numbers (the cost). Row 83 has the number of invoices (I guess to do a weighted average of the severity for the quarter). Those are all the variables. Hopefully someone can help.

For the CHOOSE function to work BR5 must be a number, so D5:BL5 are numbers too and so D6:BL6 must be month numbers - perhaps formatted to look like month names somehow(?) If the quarter number is being checked (and the year) then that will give the correct columns on its own (no need to match the months in row 6), in which case I think this simpler formula should give the same results

=SUMPRODUCT(($D$5:$BL$5=BR$5)*($D$7:$BL$7=BR$7),$D83:$BL83,$D214:$BL214)/ SUMIFS($D83:$BL83,$D$5:$BL$5,BR$5,$D$7:$BL$7,BR$7)

The SUMIFS divisor is the same