Macro to Create Total/Partial Sums

Good Morning and Nice Day to everybody in the Forum!,

Hope you are all doing great!

Just new to VBA and Macros, but trying hard to improve my skills.

At this point, I would be very glad if somebody could guide me to enhance one macro that I am developing in the excel attached.

I am actually calculating the total sales for a given car dealership considering two columns: Make and Value of the Sale. This is very straighforward with two columns. However, I am going nuts when I am trying to add a third column with the name of Sales Reps in order to show the breakdown of sales per Sales Rep and Make. Please, could you advise on this matter? Moreover, I am wondering if there is a way to sum automatically new added rows.

Many thanks in advance for any help!!

Cheers.

Hey,

I think you may be overcomplicating things and a simple SUMIFS formula would do the trick.

If you put the below in cell F2 and then drag down and right, would that get what you wanted? I can make that work for a range relative the amount of rows of data (in VBA), that is not a problem. Let me know if that is the right direction.

=SUMIFS($B$2:$B$26,$A$2:$A$26,F$1,$C$2:$C$26,$E3)

Many thanks for your reply JazzKult. I am just trying to automate the process with a macro. Just got there partially as I am able to calculate the totals with two columns, but I am not able to simulate the breakdown per Sales Rep if I include a Third Column. The idea with the formula works absolutely fine, but wondering whether it would be possible to embed it within th VBA code. Cheers.

And you could also use the "SUMPRODUCT" function

 =SUMPRODUCT(($A$2:$A$26=$F$1)*($B$2:$B$26)*($C$2:$C$26=$E$3))

Alf

Many thanks for your answers JazzKult and Alf.

I am just trying to automate the process with a macro. Just got there partially as I am able to calculate the totals with two columns, but I am not able to simulate the breakdown per Sales Rep if I include a Third Column. Any other feedback would be really welcome!!

Hi,

If you're happy with keeping formulas in the cells but want them to be populated via VBA macro then you can go with the below. Might require some tune-up if you want to add more cars/sales reps (can do that if you tell me how you'd want this to work).

Sub test()

Dim sLastRow As Single

sLastRow = Range("A1").End(xlDown).Row

Range("F3").Formula = "=SUMIFS($B$2:$B$" & sLastRow & ",$A$2:$A$" & sLastRow & ",F$1,$C$2:$C$" & sLastRow & ",$E3)"
Range("F3").Copy
Range("F3:K5").PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

End Sub

Hi JazzKult,

Many thanks again for your reply! The code provided works like a charm, however I wonder if it is possible to embed it with the Macro Button, so the calculations (both the Total Sales and Breakdown of sales) would be executed once when clicking on such button.

Best.

Ok how about this? Should be more flexible in the event you want to add more rows of data/sales reps/cars and will not keep the formulas just paste the values.

Sub Test2()
Dim rB As Range, i As Single, j As Single, lastCol As Single, lastRow As Single
 
Set rB = Range("B2", Range("B" & Rows.Count).End(xlUp))

lastCol = Range("F2").End(xlToRight).Column
lastRow = Range("E2").End(xlDown).Row

For i = 6 To lastCol 'go through columns with car names

    For j = 3 To lastRow 'go through rows with names

    Cells(j, i) = Application.WorksheetFunction.SumIfs(rB, _
    rB.Offset(0, -1), "=" & Cells(1, i), _
    rB.Offset(0, 1), "=" & Cells(j, 5))
    
    Next j
    
Next i

Hi JazzKult,

This works perfect! A+++

Many, many thanks!! Now, I'll try to make one big macro with the whole thing! Wish me lack!

Best Regards!

Happy to help. Best of luck C.!