Sumif functions in excel the start row of formula keeps changing itself
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.
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
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
=SUMPRODUCT((MonB=$B8)*(MonA=$A8),MonE)
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
Sorry, missing attachment - here it is 27399875.xls
0
GracefBaseAuthor Commented:
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.
0
Featured Post
New feature! Upgrade and increase expert visibility of your issues with Priority Questions.
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
=SUM(IF(Monday!$B$10:$B$50
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
=INDEX(Monday!$A:$A,1):IND
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
=SUMPRODUCT((MonB=$B8)*(Mo
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