Question = SUMPRODUCT quarterly average > for multiple product categories

C

collegeitdept

Board Regular
Joined
Nov 14, 2008
Messages
185
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.


Please help by downloading the attached file.

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


Thanks!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Can Anyone please help with this average (SUMProduct) formula?

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


Here is the data table below:

Month
Product
Wheel base
Platform
Views
Aug-13B ProductRegularPlatform 3B253
Aug-13C ProductRegularPlatform 3B10
Aug-13B ProductRegularPlatform 3A3407
Sep-13B ProductRegularPlatform 17527
Sep-13B ProductRegularPlatform 217910
Sep-13B ProductRegularPlatform 3B8061
Sep-13B ProductExtendedPlatform 429515
Sep-13B ProductExtendedPlatform 62215
Sep-13C ProductRegularPlatform 1425
Sep-13C ProductRegularPlatform 231
Sep-13C ProductRegularPlatform 3A664
Sep-13C ProductRegularPlatform 3B1593
Sep-13D ProductRegularPlatform 1205
Sep-13D ProductRegularPlatform 2890
Sep-13D ProductRegularPlatform 3A2437
Sep-13D ProductRegularPlatform 3B8012
Sep-13D ProductExtendedPlatform 48339
Sep-13D ProductExtendedPlatform 6312
Sep-13F ProductRegularPlatform 113
Sep-13F ProductRegularPlatform 3A143
Sep-13F ProductRegularPlatform 3B499
Oct-13A ProductRegularPlatform 3A53
Oct-13A ProductRegularPlatform 3B560
Oct-13B ProductRegularPlatform 12503
Oct-13B ProductRegularPlatform 26033
Oct-13B ProductRegularPlatform 3A1729
Oct-13B ProductRegularPlatform 3B8607
Oct-13B ProductExtendedPlatform 468249
Oct-13B ProductExtendedPlatform 61494
Oct-13C ProductRegularPlatform 1344096
Oct-13C ProductRegularPlatform 2339
Oct-13C ProductRegularPlatform 3A524
Oct-13C ProductRegularPlatform 3B2703
Oct-13D ProductRegularPlatform 197085
Oct-13D ProductRegularPlatform 25610
Oct-13D ProductRegularPlatform 3A1842
Oct-13D ProductRegularPlatform 3B10965
Oct-13D ProductRegularPlatform 528
Oct-13D ProductExtendedPlatform 479598
Oct-13D ProductExtendedPlatform 63569
Oct-13F ProductRegularPlatform 12380
Oct-13F ProductRegularPlatform 3A240
Oct-13F ProductRegularPlatform 3B1022
Oct-13G ProductRegularPlatform 3A104
Oct-13G ProductRegularPlatform 3B987
Nov-13A ProductRegularPlatform 1131
Nov-13A ProductRegularPlatform 3A765
Nov-13A ProductRegularPlatform 3B2493
Nov-13A ProductExtendedPlatform 415515
Nov-13A ProductExtendedPlatform 6980
Nov-13B ProductRegularPlatform 12635
Nov-13B ProductRegularPlatform 23834
Nov-13B ProductRegularPlatform 3A1646
Nov-13B ProductRegularPlatform 3B6084
Nov-13C ProductRegularPlatform 16717
Nov-13C ProductRegularPlatform 2148
Nov-13C ProductRegularPlatform 3A555
Nov-13C ProductRegularPlatform 3B2140
Nov-13D ProductRegularPlatform 141053
Nov-13D ProductRegularPlatform 2499
Nov-13D ProductRegularPlatform 3A2116
Nov-13D ProductRegularPlatform 3B5442
Nov-13D ProductRegularPlatform 515
Nov-13D ProductExtendedPlatform 460682
Nov-13D ProductExtendedPlatform 62943
Nov-13E ProductRegularPlatform 3A48
Nov-13E ProductRegularPlatform 3B144
Nov-13F ProductRegularPlatform 1391
Nov-13F ProductRegularPlatform 3A1246
Nov-13F ProductRegularPlatform 3B1382
Nov-13G ProductRegularPlatform 181
Nov-13G ProductRegularPlatform 3A1171
Nov-13G ProductRegularPlatform 3B3679
Nov-13G ProductExtendedPlatform 416257
Nov-13G ProductExtendedPlatform 62285
Nov-13A ProductExtendedPlatform 428781
Nov-13D ProductExtendedPlatform 411093
Nov-13G ProductExtendedPlatform 432842

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




Quarterly Averages
Q1 2013Q1 2013Q1 2013Q2 2013Q2 2013Q2 2013Q3 2013Q3 2013Q3 2013Q4 2013Q4 2013Q4 2013
RegularExtendedALLRegularExtendedALLRegularExtendedALLRegularExtendedALL
Entire company (All products)26,04020,19146,231285,912.5162,144.0448,056.5
Platform 1248,536.0248,536.0
Platform 218,83118,8318,231.58,231.5
Platform 312,54012,54029,123.529,123.5
Platform 4156,508.5156,508.5
Platform 521.521.5
Platform 65,635.55,635.5
A ProductPlatform 1
A ProductPlatform 31,9361,936
A ProductPlatform 4
A ProductPlatform 6
B ProductPlatform 1
B ProductPlatform 28,9558,955
B ProductPlatform 3
B ProductPlatform 4
B ProductPlatform 6
C ProductPlatform 1
C ProductPlatform 2
C ProductPlatform 3
D ProductPlatform 1
D ProductPlatform 2
D ProductPlatform 310,44910,449
D ProductPlatform 4
D ProductPlatform 5
D ProductPlatform 6
E ProductPlatform 3192192
F ProductPlatform 1
F ProductPlatform 3
G ProductPlatform 1
G ProductPlatform 3
G ProductPlatform 4
G ProductPlatform 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:
Upvote 0
Last edited:
Upvote 0
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
 
Upvote 0
Thank you so much Barry!!!!

It worked like a charm!


I really appreciate your help.

Happy New Year!



barry houdini said:
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
Click to expand...
 
Upvote 0
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!
















barry houdini said:
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
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Sumifs or Sum product help
Replies
5
Views
669
keef2
K
T
  • Question
SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)
Replies
4
Views
931
trbharat
T
B
  • Question
COUNTIF / SUMPRODUCT with Multiple Criteria and/or Index Match
Replies
0
Views
1K
bansaladman
B
S
  • Question
Sumproduct, Mod, Offset, If??
Replies
1
Views
550
Gerald Higgins
Gerald Higgins
N
  • Question
Sum Product Formula + Rolling months update
Replies
6
Views
2K
AhoyNC
A

Forum statistics

Threads
1,215,831
Messages
6,127,142
Members
449,363
Latest member
Yap999

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top