WorksheetFunction Sumif with Multiple Columns and Rows - looping through Budget

  • 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.


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


    Files

    Edited once, last by Carim: Removed garbled text ().

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


    Hello jan.g!


    Try this:


    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

  • 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
    1. If Cells(i, 1) <> "Units" Then


    to

    Code
    1. 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

  • 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:



    Sincerely,
    Max

  • 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
    1. If Range ("1" & i) = 0 etc...

    .


    Cheers and all the best
    Jan