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

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

Hi Jan,

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

.

Cheers and all the best
Jan

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!