Efficient Reporting - Replace sumifs with macro for specified columns

  • Hello,

    I am given a 24 month rolling "data" report each week, along with two other monthly reports ("Production" & "Inventory", which i need to use for creating a customer report.

    The example customer report is the "CPB" tab

    Currently, I am using independent formulas, in each cell, to perform these calculations (mostly sumifs and index/match criteria)

    As the report size increase, the slower the file works (sometimes not working at all - getting "stuck")

    I believe it may be easier to break down my requests into separate phases (based on most critical to least needs), so I would like to try and start with columns E through I only

    of course, if someone has a better ideas on how to make the entire report better with a macro, I would be thrilled :).

    Some Notes to help:

    Column B of the CPB tab - the Location names are fine as a manual entry because it doesn't seem to change too often - it may need an occasional name added, but rarely one ever removed

    Cell B5 is a MMM-YY reference (data validation list), which allows the user to toggle back and forth to view results based on the selected month-year.CPB VBA Example.xlsm

    ***** Columns E-I, of the CPB tab (main need for assistance - phase I)

    Columns E - I, of the CPB tab, are the columns I need to try and make more efficient, using a macro instead of the "sumifs" formulas in each individual cell
    Columns E - I of the "CPB" tab, are sumifs based from the information from the "Data" tab
    Columns E - I of the "CPB" tab are currently calculated as follows:

    I have attached a two month report example (much smaller than actual 2yr report)

    Any help/suggestions to improve the efficiency of this report would be very very greatly appreciated!

    Kind Regards,


    Sum of the Sales, from Data tab, column G
    IF, column I, of the Data Tab (Well Name), matches the well name in cell B#, of the CPB tab
    And IF, column M, of the Data Tab (Product Application), matches the headers of cells E5, F5, G5, H5 & I5, of the CPB tab
    And If, column N, of the Data tab (Invoice Mo-Yr), matches the Month/Year selected in cell B5

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!