I need to create a summary of a Database and have it provide subtotals at specified changes.
Inserting subtotals is easy of course, but what I need is a weighted average so I need the SumProduct formula instead of the subtotal formula.
I would like to have it automated using an auto-open macro, that will update the information from the DB, then sort and insert the subtotals & totals
using the sumproduct formula instead of the subtotal.
I know how to do all of this except the substitution of the formula.
Hoping someone has a solution for this.
thank you
Julie
Hi Julie,
Please upload a sample file, we cannot help you without seeing and testing the formulas.
Thanks for understanding
Catalin
Hi Catalin,
I have uploaded a sample file of the summary data.
I inserted subtotals which of course is very easy - but then I had to manually add the weighted average lines below each subtotal line.
I don't need the subtotal lines- just the weighted average lines.
I am hoping for some VBA code that will insert these weighted average lines, but I will appreciate any method that will automate this.
Thank you
Julie
Hi Julie,
You have to start learning pivot tables, it will save you a lot of time.
See the attached file for a sample pivot table report. I added the calculated column only for Area 1, you have to do the same for your other columns. For the weighted average, I added a calculated field (you will find the field in Pivot Table tools Tab in ribbon, Analyze-Field,Items and Sets). You have to do the same to add this calculated field for the other columns you want to show in report.
Hope this is what you wanted.
Cheers,
Catalin
Hi Catalin,
I do use pivot tables and slicers all the time, I did not however know how to add the calculated field so I didn't think it would work for me in this instance.
Thank you so much for your help!
Julie.
You're welcome 🙂
