Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22

Thread: Concurrent SUM & SUMPRODUCT calculation in a dynamic range.

  1. #1

    Exclamation Concurrent SUM & SUMPRODUCT calculation in a dynamic range.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I just joined XLguru and this is my first post.

    I have one issue for which i would need your help/feedback.

    This is my issue:
    I have a master data in "Sheet1" containing info about Month, Region, Activity, Nbr of cartons and cycle time (in decimal),...


    MonthRegionActivityCTCarton2GCIR23271GCIR24291SAIR11102SAIR23282SAIR24231SAIR24232SAMFE3732SAMFE371


    With a VBA program, excel copies the data in "Sheet2" and proceed to a sorting + inserting an empty row to separate the data per month, region, activity, as follow:

    Code as below:

    Code:
     
    Sub WorkSheetSort1()
    Dim Rw As Range
    Dim Column As Long
     
    Application.ScreenUpdating = False
     
    Worksheets("Master").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    Range(Selection, cells(1)).Select
     
    For Each Rw In Selection.Rows
    Column = Rw.Row
    If Rw.cells(1, 1).Value <> " " Then
    Rw.Copy Destination:=Worksheets("Region").cells(Column, 1).EntireRow
    End If
     
    Next Rw
     
    Worksheets("Master").Select
    Range("A1").Select
    Worksheets("Region").Select
    Range("A:U").Sort Key1:=Range("C2"), Order1:=xlAscending, _
    Key2:=Range("B2"), Order2:=xlYes, Header:=xlYes, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom
     
    Call myMonth
    Call InsertRows1
    Call mySubT1
    Call myAutoFit
     
    End Sub

    And i obtain the following result:

    MonthRegionActivityCTCarton1GCIR24.229?292GCIR23.127?271SAIR11101SAIR24.223?332SAIR23.1282SAIR24.223?512SAMFE3732SAMFE371?4

    The above data are in the following column:
    Month = Column B
    Region = Column C
    Activity = Column D
    CT = Column J
    Carton = Column M

    As you can see the above data, after sorting, looks like blocks of data.

    To calculate the SUM of my nbr of cartons and to insert the result in the empty row, I am using the following code:

    Code:
     
    Sub mySubT1()
     
    'Do Subtotal SUM
    For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
    Rows(aArea.Row + aArea.Rows.Count).Font.ColorIndex = 5
    Rows(aArea.Row + aArea.Rows.Count).Font.Bold = True
        cells(aArea.Row + aArea.Rows.Count, 13).Formula = "=SUM(" & Range(cells(aArea.Row, 13), cells(aArea.Row + aArea.Rows.Count - 1, 13)).Address & ")"
        cells(aArea.Row + aArea.Rows.Count, 14).Formula = "=SUM(" & Range(cells(aArea.Row, 14), cells(aArea.Row + aArea.Rows.Count - 1, 14)).Address & ")"
        cells(aArea.Row + aArea.Rows.Count, 19).Formula = "=SUM(" & Range(cells(aArea.Row, 19), cells(aArea.Row + aArea.Rows.Count - 1, 19)).Address & ")"
        cells(aArea.Row + aArea.Rows.Count, 20).Formula = "=SUM(" & Range(cells(aArea.Row, 20), cells(aArea.Row + aArea.Rows.Count - 1, 20)).Address & ")"
     
    Next aArea
    End Sub

    Now I would need to calculate for each block of data the SumProduct of the CT(col10) versus the Carton(col13): =SUMPRODUCT(CT(col10)*Carton(col13))/TotCarton(col13)
    And insert the result in the empty row in the corresponding column (see "?")

    This knowing that the above data is dynamic, so i cannot determine a fix range like ("C2:C7"), because the lengh of those ranges will change every month.

    Please let me know if you can help me coding this SUMPRODUCT portion for my case.

    Thanks in advance for your help,
    Lomic6

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi there, and welcome to the forum!

    Is there any way that you can upload a sample workbook with data in it? I'm not sure that I'm quite visualizing the output as it is in the workbook... You refer to columns, but what you have above is one great long string of text. Is that intentional?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken,

    Thanks for your reply.
    Ok, it seems i faced format issue and the great long string of text was not intentional at all.
    Sorry for that.

    Please find as attached my file.
    If you check on the sheet "Region" you will see that after sorting per Region and month i get some "blocks of data".
    I need to calculate for each "blocks of data" the sumproduct of the column J with the column M, then column K with column M, etc....

    Basically, the extraction of data will be done on monthly basis.

    The difficulty is since the data is a dynamic range, next month the example of area J2:J4, may become J2:J10 or only J2, and the inserted row will not be at the same place as well, from one month to the other one. but the results of my calculation must always be in that inserted row.

    Hope the above info will help to clarify, and with the attached file.

    Thanks again for your good help,
    Lomic6
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi Lomic,

    Sorry for the late reply here, it's been nutty busy over the last few days.

    Out of curiosity, can you make the month longer? I'm not sure I have the formula correct as I'm not quite getting the whole gist of the calculation, but if you just want to multiply ColJ by ColM: =SUMPRODUCT(--($B$1:$B4=$B4),J$1:J4,M$1:M4)

    So if you put that in row 5, it would multiply all the col J by col M entries where the Month equals what is in B4.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Hi Ken,

    Yes, generally speaking I want to multiply ColJ by ColM for each block of data that will appear after the sorting step. But i need also the program to divide, for each block of data, the sumproduct ColJ*ColM result by the respective block sum ColM result.
    I don't want to have to put the formula in Row 5 manually. I need the program to detect the empty fields automatically and to insert automatically the information.

    Have you been able to run the macro on my previous file ?
    I have made the month longer in the Sheet "Region" after the sorting (see attached)

    Cheers,
    Lomic
    Attached Files Attached Files

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Can I get you to just step back a bit on this one? I totally misread the first part of the post. Let's regress back to the first example file you gave here.

    So, I get that you've got your SUM formulas in M, N, S and T

    Where are these SUMPRODUCT formulas supposed to go? I.e. What is the formula that you'd expect to in J5, K5, O5 P5 and Q5? To me, then lend to totals, not sumproducts.

    (FYI, I'm going to be out of town from Sunday, but if I'll try and get you sorted before I leave if I can.)

    Cheers,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    I think the code below will set the formulae for both your SUM and SUMPRODUCT requirements in the sheet

    Code:
    Option Explicit
    Sub mySubT2Region()
    
        Dim aArea As Range
        Dim fr As Long, lr As Long, x As Long
    
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Worksheets("Region").Select
    
        For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
            Rows(aArea.Row + aArea.Rows.Count).Font.ColorIndex = 3
            Rows(aArea.Row + aArea.Rows.Count).Font.Bold = True
    
            fr = Application.Max(2, aArea(1).Row)
            lr = aArea(aArea.Cells.Count).Row
            x = lr - fr + 1
            ' Fill in SUM's
            Cells(lr + 1, 13).FormulaR1C1 = "=SUM(R[-" & x & "]C:R[-1]C)"
            Cells(lr + 1, 14).FormulaR1C1 = "=SUM(R[-" & x & "]C:R[-1]C)"
            Cells(lr + 1, 18).FormulaR1C1 = "=SUM(R[-" & x & "]C:R[-1]C)"
            Cells(lr + 1, 19).FormulaR1C1 = "=SUM(R[-" & x & "]C:R[-1]C)"
            Cells(lr + 1, 20).FormulaR1C1 = "=SUM(R[-" & x & "]C:R[-1]C)"
            'Fill in SUMPRODUCTS
            Cells(lr + 1, 10).FormulaR1C1 = "=SUMPRODUCT(R[-" & x & "]C:R[-1]C,R[-" & x & "]C[3]:R[-1]C[3])/RC[3]"
            Cells(lr + 1, 11).FormulaR1C1 = "=SUMPRODUCT(R[-" & x & "]C:R[-1]C,R[-" & x & "]C[2]:R[-1]C[2])/RC[2]"
            Cells(lr + 1, 12).FormulaR1C1 = "=SUMPRODUCT(R[-" & x & "]C:R[-1]C,R[-" & x & "]C[1]:R[-1]C[1])/RC[1]"
            Cells(lr + 1, 15).FormulaR1C1 = "=SUMPRODUCT(R[-" & x & "]C:R[-1]C,R[-" & x & "]C[3]:R[-1]C[3])/RC[3]"
            Cells(lr + 1, 16).FormulaR1C1 = "=SUMPRODUCT(R[-" & x & "]C:R[-1]C,R[-" & x & "]C[2]:R[-1]C[2])/RC[2]"
            Cells(lr + 1, 17).FormulaR1C1 = "=SUMPRODUCT(R[-" & x & "]C:R[-1]C,R[-" & x & "]C[1]:R[-1]C[1])/RC[1]"
    
        Next aArea
    
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    
    End Sub
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  8. #8
    Hi Roger,

    It is amazing, and it work perfectly !!!
    I am not familiar at all with the R1C1 language.
    Is there a way to translate this coding into a VBA coding format like my previous one ?

    Anyway thank you so much for your GREAT help !

    Cheers,
    Lomic

  9. #9
    Or do you mind to ellaborate your R1C1 coding for me to understand, correctly ?

    Thank again,
    Lomic

  10. #10
    R1C1 just uses numeric column and row ids, whereas the A1 notation uses letters for the columns. It is not absolutely necessary, but in VBA it is often convenient because formulas are often set in loops, and loops have a numeric index.

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •