SUM on varying number of cells in VBA

Hi, mathmani,

sorry but I couldnīt find any code in your sample workbook...

Please Login or Register  to view this content.

Ciao,
Holger

Thanks HaHoBe. This has worked perfectly with the example I posted. Now when I am trying to further expand it to use SUMPRODUCT with more conditions added, it's failing (And I think that is because I am trying to mix styles of writing formulas). Would you be able to look at it please ? Attached is the excel with final fomula that I am trying to print and below is theexcerpt of the overall code I have written. The additional formula is highlighted in maroon color which seems to be giving problem -

Do While ActiveCell.Value <> ""
Set rngMerged = ActiveCell.MergeArea
If rngMerged.Cells.Count > 0 Then
Cells(lngLast + 1, rngMerged.Cells(1).Column).Formula = "=SUMPRODUCT(" & Cells(3, rngMerged.Cells(1).Column).Address(0, 0) & ":" & _
Cells(lngLast - 1, rngMerged.Cells(rngMerged.Cells.Count).Column).Address(0, 0) & ")*($A$3:$A$" & n & "=""RP1"")*($C$3:$C$" & n & "=""" & RP(i, 1) & """))"
End If
ActiveCell.Offset(0, 1).Select
Loop
'''' ---- RP(i,1) will contain names (in the excel "Central Team", "BBB", etc.)

Hi, mathmani,

variables n and i are not declared, and Iīm afraid I donīt see how you want these to be combined as only one formula was inserted which doesnīt cover the whole range build in the left 3 columns but is one short on rows.

Ciao,
Holger

variables i and n are defined at start of the code and when the flow enters above code for first time, value of m and n are 25 and 23 respectively. As I had mentioned above written code was only an extract. I don't want to cover the last row of the data, and the excel which I had uploaded was also a sample. If I can summarise the requirement, I would want to use SUMPRODUCT function for each combination of values in Column A and Column C. This SUMPRODUCT will be used for all weeks under a month and need to be repeated for all months and as you can see number of weeks in a month can change. does it clarify ?

Hi, mathmani,

wouldnīit be a great idea to share all the code you have so far and not only a sniplet which canīt work due to a missing opening bracket (check the formula inserted manually where 2 opening brackets appear against the code) and only one item in a list referred to?

Maybe you can use the approach like this:

Please Login or Register  to view this content.

Ciao,
Holger