Need a dynamic sumproduct for visible cells in a vertical array HELP!!!!

[Josh]

This is a big one. I have some customer data inputs at the warehouse level that I need to summarize into larger groups of warehouses that we have called "SELL TO's". The data file has over 10K rows and has filters applied to it. There are 8 different product groups, 192 customer "SHIP TO's" aka(warehouses) and roughly 17 "SELL TO's" I have used the sumifs function at the top of the sheet to sum certain rows of data. That was easy enough, but what I need next is the total weighted growth% and the total weighted share% for the whole "SELL TO".

Ultimately, I need a dynamic way to sumproduct only visible cells with multiple conditions:

(Invoiced sales of CustomerA x Growth of CustomerA "aka CAGR Override")
+
(Invoiced sales of CustomerB x Growth of CustomerB "aka CAGR Override")
+
(Invoiced sales of CustomerC x Growth of CustomerC "aka CAGR Override")
=
Overall growth for SELL TO group

The example shown just has three customers that need to roll up to one growth and one share. But in other instances on this sheet there are many many customers that need to be rolled up into one group. The formula shown is the manual approach to show my concept but I need a formula that will do this for me. Also, the result for this example should be 9.5% growth.

 

[Luke M]

Hi Josh,

With such a large data set, have you considered using a PivotTable? That would let you do the filtering easier, and you wouldn't face the challenge of "visible cells only". Then, if needed, we can extract the required info from the PT and perform the necessary calculations, e.g. Invoiced Sales + Growth.

 

[Josh]

Hi Josh,

With such a large data set, have you considered using a PivotTable? That would let you do the filtering easier, and you wouldn't face the challenge of "visible cells only". Then, if needed, we can extract the required info from the PT and perform the necessary calculations, e.g. Invoiced Sales + Growth.

Ok lets try this. At this point I am in dire need I have a reporting deliverable on may 8 and need all of this data summarized. I really appreciate your help!

 

[Luke M]

Can you upload a sample workbook of your data and some idea of the layout you need? Working from a picture in a PDF is very hard.

 

[Somendra Misra]

Hi Josh,

See the attached file.

Don't know how much this will be helpful, as there was no sample file. I had created the formula you have shown as sample. Try to adjust the range in your original file and see if the formula works for you.

Formula in G23:

=SUM(SUBTOTAL(109,OFFSET(C2:C22,(CEILING(ROW($C$1:$C$21)/3,1)-1)*3,,1,1))*(SUBTOTAL(9,OFFSET(C2:C22,ROW(C2:C22)-MIN(ROW(C2:C22)),,1,1))*(B2:B22="CAGR")))/SUM(SUBTOTAL(109,OFFSET(C2:C22,ROW(C2:C22)-MIN(ROW(C2:C22)),,1))*(B2:B22="Sales"))

Note this is an array formula, and must be entered with Ctrl+Shift+Enter.



Regards,