WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

jan.g · Jun 19th 2017

Hi All

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.

Code

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]

Display More

Very much appreciate any ideas.
Cheers and have a wonderful day.

Max1616 · Jun 20th 2017

Re: WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

Hello jan.g!

Try this:

Code

Sub Totals()


Dim lrow As Long
Dim i As Long
Dim j As Integer
lrow = Cells(Rows.Count, 2).End(xlUp).Row


For i = 4 To lrow
    For j = 11 To 15
        If Cells(i, 1) <> "Units" Then
            Cells(i, j) = WorksheetFunction.SumIfs(Range(Cells(i, 1), Cells(i, 10)), Range("A3:J3"), Cells(3, j))
        Else
            Cells(i, j) = Cells(i, j - 5)
        End If
    Next j
Range("A" & i & ":E" & i).Copy
Range("K" & i & ":O" & i).PasteSpecial Paste:=xlPasteFormats
Next i


End Sub

Display More

I think this is what you are looking for, let me know if you have any questions!

Sincerely,
Max

Edit: Quick change to the code

jan.g · Jun 20th 2017

Re: WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

Hello Max,
Thank you so much for the extremely fast response. You either work really late or you live somewhere in the Sthn hemisphere.
I have found this site invaluable in trying to learn VBA as a beginner. It's much harder on a Mac as it is difficult to debug and there are no helpers when typing code.
Your code was perfect.
Just 1 thing, I would like to remove those lines that have no value so they should not total. These blank rows just improve the visual. I added a column to the end and put letters in each cell to mimic a real report description. I tried to change

Code

If Cells(i, 1) <> "Units" Then

to

Code

If (Cells(i,1)<> "Units" or Cells(i,16) <> "") Then

but it still calculates a total for those lines. I also tried "isEmpty". Any ideas.

Cheers
Jan

Max1616 · Jun 20th 2017

Re: WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

Hi Jan,

I'm glad you find this site helpful!

I think I understand what you are trying to accomplish. Try this:

Code

Sub Totals()
     
    Dim lrow As Long
    Dim i As Long
    Dim j As Integer
    lrow = Cells(Rows.Count, 2).End(xlUp).Row
     
    For i = 4 To lrow
        For j = 11 To 15
            If Cells(i, 1) <> "Units" Then
                Cells(i, j) = WorksheetFunction.SumIfs(Range(Cells(i, 1), Cells(i, 10)), Range("A3:J3"), Cells(3, j))
            Else
                Cells(i, j) = Cells(i, j - 5)
            End If
        Next j
        Range("A" & i & ":E" & i).Copy
        Range("K" & i & ":O" & i).PasteSpecial Paste:=xlPasteFormats
        If Range("l" & i) = 0 Or Len(Range("l" & i)) = 0 Then Range("K" & i & ":O" & i) = ""
    Next i
     
End Sub

Display More

Sincerely,
Max

jan.g · Jun 22nd 2017

Re: WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

Hello Max,

Thank you again. The above solution worked perfectly.
Just a quick question as I am trying to learn VBA...could you explain why there doesn't need to be any "End If" after the additional line

Code

If Range ("1" & i) = 0 etc...

.

Cheers and all the best
Jan

WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

Participate now!

Share