I have spent many days trying to work this out but am now at the point of pulling my hair out.
My spreadsheet (copy attached) is a P&L Budget with multiple columns divided into Stages. Each Stage has a number of subheadings, Eg Units, Total,2017, 2018, 2019.
In my actual spreadsheet, I have used named ranges for the Sum Range, Lookup Range and Lookup Value
For the Overall Totals my formula is =SUMIF(Titles,TotHead,AllStages)
I would like to achieve the following:
- Sum the totals for each row under the correct subheading.
- Copy the formatting
- Skip header and blank rows
I have this code which works for 1 column only totals. How can I adapt it to total all columns using the SUMIF Worksheet Function across all columns, skipping headers and blank rows.
Sub SumTotals() Dim total As Double For iRow = 5 To 70 total = 0 For iCol = 1 To 10 If (Cells(3, iCol).Value) = "2017" Then total = total + Cells(iRow, iCol).Value End If Next iCol Cells(iRow, 11).Value = total Next iRow [COLOR=#FFFFFF][FONT=Menlo]End Sub[/FONT][/COLOR]
Very much appreciate any ideas.
Cheers and have a wonderful day.