Is there a quicker way to calculate the cells highlighted in green? [SOLVED]

Is there a quicker way to calculate the cells highlighted in green? Due to company policy, the report format has to stay as it is and cannot be changed. This could become quite a manual task if there are, say, up to Company Z.

Column B is always either No.1 Plan or No.2 Plan. Column D is always Commission.

No PivotTable. I have used the SUBTOTAL function, just the manual process I want to eliminate.

There might not be an answer to this but I really appreciate any help.

Okay, here are 3 formulae that will accomplish what you want, using the SUBTOTAL function's feature of not counting any other SUBTOTALs that it encounters, and the fact that INDEX can be used with colons to produce dynamic cell ranges.

So, the easy one is the Grand Total in cell E23:

=SUBTOTAL(9,E$2:E22)

This just SUBTOTALs the entire column above it.

Next easiest is the Product Subtotals in E3, E14, and E17: (Formula entered as in E3; once pasted there, you can just copy/paste it into the others. Throwing a Filter on your workbook will let you Filter Column B for "Not Blank" and "not 'Subtotal:'" to do this en masse)

=SUBTOTAL(9,E4:INDEX(E4:E109,MATCH(1,--(""&D4:D105=""),0)-1))

This looks down Column D to find the next Blank Cell, and then SUBTOTALs all of the cells in Column E between that row and the current row, exclusive.

(Note: this will throw an error if you have more than 100 Customers for a particular Product. You can fix that by changing ""&D4:D105="" to ""&D4:D$1048576="", at the cost of increasing how long it takes to Calculate…)

Finally, you have the Company Subtotals in Cells E11 and E21: (Formula entered as in E11. As with the previous example, you can just filter for Column B is "Subtotal" to make this easier)

=SUBTOTAL(9, E10:INDEX(E:E,MAX(ROW(A$1:A11)*--(LEN(A$1:A11)>0))))

This looks up Column A, to find the previous non-Blank row, then SUBTOTALs everything from that row to the row before the current row.

If needed, you could probably put together a VBA script that adds these formulae to the appropriate cells (N.B. working with R1C1 reference instead of A1 reference will make that easier, although at that point you may as well stick with A1 references and calculate each Range in turn individually…), then save it to your Personal.XLSM, and add a button to your Ribbon or Quick Access Toolbar that will apply it to the current ActiveSheet.

Open the report, click the button, done.