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.


    [TABLE="class: grid, width: 500"]

    [tr]


    [td][/td]


    [td]

    Stage1

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Stage2

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Totals

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Units

    [/td]


    [td]

    Total

    [/td]


    [td]

    2017

    [/td]


    [td]

    2018

    [/td]


    [td]

    2019

    [/td]


    [td]

    Units

    [/td]


    [td]

    Total

    [/td]


    [td]

    2017

    [/td]


    [td]

    2018

    [/td]


    [td]

    2019

    [/td]


    [td]

    Units

    [/td]


    [td]

    Total

    [/td]


    [td]

    2017

    [/td]


    [td]

    2018

    [/td]


    [td]

    2019

    [/td]


    [/tr]


    [tr]


    [td]

    Bananas

    [/td]


    [td]

    5

    [/td]


    [td]

    60

    [/td]


    [td]

    10

    [/td]


    [td]

    20

    [/td]


    [td]

    30

    [/td]


    [td]

    10

    [/td]


    [td]

    60

    [/td]


    [td]

    25

    [/td]


    [td]

    25

    [/td]


    [td]

    10

    [/td]


    [td]

    15

    [/td]


    [td]

    120

    [/td]


    [td]

    35

    [/td]


    [td]

    45

    [/td]


    [td]

    40

    [/td]


    [/tr]


    [tr]


    [td]

    Apples

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    TOTAL SALES

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Units

    [/td]


    [td]

    Total

    [/td]


    [td]

    2017

    [/td]


    [td]

    2018

    [/td]


    [td]

    2019

    [/td]


    [td]

    Units

    [/td]


    [td]

    Total

    [/td]


    [td]

    2017

    [/td]


    [td]

    2018

    [/td]


    [td]

    2019

    [/td]


    [td]

    Units

    [/td]


    [td]

    Total

    [/td]


    [td]

    2017

    [/td]


    [td]

    2018

    [/td]


    [td]

    2019

    [/td]


    [/tr]


    [tr]


    [td]

    Expenses

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Total Expenses

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • 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