Sumif functions in excel the start row of formula keeps changing itself

Posted on 2011-10-17
Last Modified: 2012-05-12
I have a workbook template where we enter the data each week and there is a summary sheet using sumif functions.  I've checked the functions on the template and they are perfect
eg =SUM(IF(Monday!$B$1:$B$500=$B8,IF(Monday!$A$1:$A$500=$A8,Monday!$E$1:$E$500,0),0))
I did have the formula selecting the whole column ie B, but the calculations were too much for my colleagues computer to cope with so she kept getting NUM errors.  Therefore I selected a range big enough for the data rows which can vary from week to week.  There is a really strange problem in that on the first row of the sumif column the formulas keep changing themselves to be instead of starting $B$1 to say $B$10 so some of the data is missing from the calculation. On the Monday sheet there is data above this row, so I cannot figure out how or why the formulas are changing themselves - its definitely happening as I have to change it back again and again once the data has been input on each sheet - has anybody any idea and how to fix?
My colleague is using Office 2003.  I have tried redoing and saving in 2003 and 2010 compatibility mode.
Question by:GracefBase
    LVL 50

    Accepted Solution

    Hello GracefBase,

    Is it possible that rows are inserted in the Monday sheet at the top? If 9 rows were inserted, for instance, then the formula in Summary sheet would change to ths


    i.e. all ranges are pushed down by 9 rows.

    If rows are to be added to Monday sheet then they should be added below row 1 - that will still extend the ranges in your formulas....but they'll still start at row 1 so all the data will be covered

    You could perhaps consider using dynamic named ranges in the Monday worksheet. If you define a named range MonA based on the number of entries in that column (with COUNTA), i.e. using this formula


    and also apply named ranges to columns B and E in the same way then your formula can be reduced to this non-array version


    See attached example

    Try adding another entry in Monday with "x" in A51, "y" in B51 and any number in E51 - notice that this amount will automatically be added to the total in C9

    Note that the named ranges only work if one column in Mondays sheet will have continuous values from row 1 - then the named ranges for all columns can be based on that one - I used column A for that purpose

    regards, barry
    LVL 50

    Expert Comment

    by:barry houdini
    Sorry, missing attachment - here it is

    Author Closing Comment

    Thank you so much it was because of data being inserted before the first row.  Thanks also for the suggestion on dynamic range and example which I will look at implementing in future.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
    Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now