# Question = SUMPRODUCT quarterly average > for multiple product categories

#### collegeitdept

##### Board Regular
Hello,

I have data table with sales numbers by product, its wheel base, and over platform for months ranging from Aug 2013 - Nov 2013. This data table will populate with the complete month sales numbers after the month has finished going forward.

I have a table to the right of the listing the sum totals for each product (by wheel base and platform) by each quarter (rolling).

Now I need help building a sumproduct average to calculate the quarterly average. This is not that simple because not all products (platform/wheel base) were available the entire quarter.

Also PLATFORM 3 in the tables are grouped - instead of listing platform 3a and platform 3b - using this in the formula: (LEFT(\$D\$2:\$D\$100,LEN(\$J9))=\$J9)

Because not all products were available for the entire quarter - and the formula will need to count how many months that product, platform, wheel base was available during that quarter I'm guessing "=MOD(MONTH(A1)-1,3)+1" this may have to be used to count the number of months into the quarter the product was available.

https://dl.dropboxusercontent.com/u/15717201/Excel Question - quarterly average.xlsx

Thanks!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

https://dl.dropboxusercontent.com/u/15717201/Excel Question - quarterly average.xlsx

Here is the data table below:

 Month Product Wheel base Platform Views Aug-13 B Product Regular Platform 3B 253 Aug-13 C Product Regular Platform 3B 10 Aug-13 B Product Regular Platform 3A 3407 Sep-13 B Product Regular Platform 1 7527 Sep-13 B Product Regular Platform 2 17910 Sep-13 B Product Regular Platform 3B 8061 Sep-13 B Product Extended Platform 4 29515 Sep-13 B Product Extended Platform 6 2215 Sep-13 C Product Regular Platform 1 425 Sep-13 C Product Regular Platform 2 31 Sep-13 C Product Regular Platform 3A 664 Sep-13 C Product Regular Platform 3B 1593 Sep-13 D Product Regular Platform 1 205 Sep-13 D Product Regular Platform 2 890 Sep-13 D Product Regular Platform 3A 2437 Sep-13 D Product Regular Platform 3B 8012 Sep-13 D Product Extended Platform 4 8339 Sep-13 D Product Extended Platform 6 312 Sep-13 F Product Regular Platform 1 13 Sep-13 F Product Regular Platform 3A 143 Sep-13 F Product Regular Platform 3B 499 Oct-13 A Product Regular Platform 3A 53 Oct-13 A Product Regular Platform 3B 560 Oct-13 B Product Regular Platform 1 2503 Oct-13 B Product Regular Platform 2 6033 Oct-13 B Product Regular Platform 3A 1729 Oct-13 B Product Regular Platform 3B 8607 Oct-13 B Product Extended Platform 4 68249 Oct-13 B Product Extended Platform 6 1494 Oct-13 C Product Regular Platform 1 344096 Oct-13 C Product Regular Platform 2 339 Oct-13 C Product Regular Platform 3A 524 Oct-13 C Product Regular Platform 3B 2703 Oct-13 D Product Regular Platform 1 97085 Oct-13 D Product Regular Platform 2 5610 Oct-13 D Product Regular Platform 3A 1842 Oct-13 D Product Regular Platform 3B 10965 Oct-13 D Product Regular Platform 5 28 Oct-13 D Product Extended Platform 4 79598 Oct-13 D Product Extended Platform 6 3569 Oct-13 F Product Regular Platform 1 2380 Oct-13 F Product Regular Platform 3A 240 Oct-13 F Product Regular Platform 3B 1022 Oct-13 G Product Regular Platform 3A 104 Oct-13 G Product Regular Platform 3B 987 Nov-13 A Product Regular Platform 1 131 Nov-13 A Product Regular Platform 3A 765 Nov-13 A Product Regular Platform 3B 2493 Nov-13 A Product Extended Platform 4 15515 Nov-13 A Product Extended Platform 6 980 Nov-13 B Product Regular Platform 1 2635 Nov-13 B Product Regular Platform 2 3834 Nov-13 B Product Regular Platform 3A 1646 Nov-13 B Product Regular Platform 3B 6084 Nov-13 C Product Regular Platform 1 6717 Nov-13 C Product Regular Platform 2 148 Nov-13 C Product Regular Platform 3A 555 Nov-13 C Product Regular Platform 3B 2140 Nov-13 D Product Regular Platform 1 41053 Nov-13 D Product Regular Platform 2 499 Nov-13 D Product Regular Platform 3A 2116 Nov-13 D Product Regular Platform 3B 5442 Nov-13 D Product Regular Platform 5 15 Nov-13 D Product Extended Platform 4 60682 Nov-13 D Product Extended Platform 6 2943 Nov-13 E Product Regular Platform 3A 48 Nov-13 E Product Regular Platform 3B 144 Nov-13 F Product Regular Platform 1 391 Nov-13 F Product Regular Platform 3A 1246 Nov-13 F Product Regular Platform 3B 1382 Nov-13 G Product Regular Platform 1 81 Nov-13 G Product Regular Platform 3A 1171 Nov-13 G Product Regular Platform 3B 3679 Nov-13 G Product Extended Platform 4 16257 Nov-13 G Product Extended Platform 6 2285 Nov-13 A Product Extended Platform 4 28781 Nov-13 D Product Extended Platform 4 11093 Nov-13 G Product Extended Platform 4 32842

<colgroup><col style="width: 69px"><col width="69"><col width="69"><col width="96"><col width="69"></colgroup><tbody>
</tbody>

 Quarterly Averages Q1 2013 Q1 2013 Q1 2013 Q2 2013 Q2 2013 Q2 2013 Q3 2013 Q3 2013 Q3 2013 Q4 2013 Q4 2013 Q4 2013 Regular Extended ALL Regular Extended ALL Regular Extended ALL Regular Extended ALL Entire company (All products) 26,040 20,191 46,231 285,912.5 162,144.0 448,056.5 Platform 1 248,536.0 248,536.0 Platform 2 18,831 18,831 8,231.5 8,231.5 Platform 3 12,540 12,540 29,123.5 29,123.5 Platform 4 156,508.5 156,508.5 Platform 5 21.5 21.5 Platform 6 5,635.5 5,635.5 A Product Platform 1 A Product Platform 3 1,936 1,936 A Product Platform 4 A Product Platform 6 B Product Platform 1 B Product Platform 2 8,955 8,955 B Product Platform 3 B Product Platform 4 B Product Platform 6 C Product Platform 1 C Product Platform 2 C Product Platform 3 D Product Platform 1 D Product Platform 2 D Product Platform 3 10,449 10,449 D Product Platform 4 D Product Platform 5 D Product Platform 6 E Product Platform 3 192 192 F Product Platform 1 F Product Platform 3 G Product Platform 1 G Product Platform 3 G Product Platform 4 G Product Platform 6

<colgroup><col style="width: 69px"><col width="117"><col width="69"><col width="69"><col width="69"><col width="69"><col width="69"><col width="69"><col width="69"><col width="69"><col width="69"><col width="90"><col width="90"><col width="90"></colgroup><tbody>
</tbody>

https://dl.dropboxusercontent.com/u/...20average.xlsx

Last edited:

That doesn't work - it's for quarter TOTAL - not average.

Using that the sum product to "count" as the denominator - does not work because it will count the number instances the product appears (denominator is wrong) and not the number of months to calculate the quarterly average.

Last edited:
Really.... no one can help with a average and sum product question???

For the number of months (your denominator) try using FREQUENCY function, e.g. for AG6

=SUM(IF(FREQUENCY(IF((ROUNDUP(MONTH(\$A\$2:\$A\$100)/3,0)=MID(AG\$4,2,1)+0)*(YEAR(\$A\$2:\$A\$100)=VALUE(RIGHT(AG\$4,4)))*(\$C\$2:\$C\$100=AG\$5),MONTH(\$A\$2:\$A\$100)),{1,2,3,4,5,6,7,8,9,10,11,12}),1))

confirmed with CTRL+SHIFT+ENTER

Obviously you need to use that in conjunction with the total from your sum total table

Thank you so much Barry!!!!

It worked like a charm!

Happy New Year!

For the number of months (your denominator) try using FREQUENCY function, e.g. for AG6

=SUM(IF(FREQUENCY(IF((ROUNDUP(MONTH(\$A\$2:\$A\$100)/3,0)=MID(AG\$4,2,1)+0)*(YEAR(\$A\$2:\$A\$100)=VALUE(RIGHT(AG\$4,4)))*(\$C\$2:\$C\$100=AG\$5),MONTH(\$A\$2:\$A\$100)),{1,2,3,4,5,6,7,8,9,10,11,12}),1))

confirmed with CTRL+SHIFT+ENTER

Obviously you need to use that in conjunction with the total from your sum total table

Hi Barry,

One small adjustment that shouldn't be too hard.

What would be the formula (denominator when calculating averages) for weeks (instead of months/quarters)?

IE: 2 week average, 2 week average for the prior week, 4 week average, 6 week average

https://dl.dropboxusercontent.com/u/15717201/Excel Question - quarterly average.xlsx

See the attached in the "weeks" tab.

The weekly average table will have the start and end of the relevant date range for easier calculations when writing the formula.

Thanks!

For the number of months (your denominator) try using FREQUENCY function, e.g. for AG6

=SUM(IF(FREQUENCY(IF((ROUNDUP(MONTH(\$A\$2:\$A\$100)/3,0)=MID(AG\$4,2,1)+0)*(YEAR(\$A\$2:\$A\$100)=VALUE(RIGHT(AG\$4,4)))*(\$C\$2:\$C\$100=AG\$5),MONTH(\$A\$2:\$A\$100)),{1,2,3,4,5,6,7,8,9,10,11,12}),1))

confirmed with CTRL+SHIFT+ENTER

Obviously you need to use that in conjunction with the total from your sum total table

Last edited:
@ barry houdini

Hi Barry!

I have that one last question... just a variant of the formula you provided. Modify it instead of monthly averages - but for weekly averages.

Thanks!

Replies
5
Views
361
Replies
4
Views
806
Replies
0
Views
933
Replies
1
Views
480
Replies
6
Views
2K

1,203,242
Messages
6,054,359
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back