SUMIF with the ranges from another sheet

  • Hello, I am a beginner in VB Excel and would like to know on how I can workaround with my problem.


    In my file, I have two sheets, the first sheet is where I can possibly update the file (invoices) while the other is where I need to get the sum of the values from the first sheet.


    What I need to do is to do a sumif in the second sheet. I can do this with an excel formula, however, I need to this for the next 3 years (36 months) and I cannot simply copy the sumif formula since there are columns in between the months.


    The code for my second sheet is as follows:



    The parts italicized and underlined is where I am getting an error.


    I am not really sure on how I can define the three ranges and on how I can possibly loop it for the next months.. :(


    Thank you very much in advance for your responses!


    Godspeed!

  • Re: SUMIF with the ranges from another sheet


    Hi HH


    Welcome to Ozgrid. I want to see your workbook. Yes I am sure there is very sensitive information on it. Create something that has the same format and put dummy data in if this is the case. I am sure you don't want people to create a workbook when you have one in front of you.


    Take care


    Smallman

  • Re: SUMIF with the ranges from another sheet


    Hello Smallman! Thank you very much for your response. I will post the workbook as soon as I translate it from German.. Please give me 5 minutes, Google Translate gives me a headache. :karate:

  • Re: SUMIF with the ranges from another sheet


    Hi HH


    I have had a quick look at your data and the simpliest and I believe the best solution is to insert 4 columns between each month in your Summary sheet. Now copy your Sumif formula across to all the monthly totals. Now make a copy of the sheet and call it Template. Now on the real sheet delete the columns you just inserted.


    Because your data is consistent on the Standard Billing page you can use the following method with confidence. No vb required. I am sure this occured to you however often the simpliest solution is the best.


    Take care


    Smallman

  • Re: SUMIF with the ranges from another sheet


    Thank you for the solution. I was thinking on how to automatically update the sumifs formula as well, the very reason why I am placing the last row number of data in the second sheet.


    I applied your solution Smallman, thank you very much for your time. :)