[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

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.
0
GracefBase
Asked:
GracefBase
  • 2
1 Solution
 
barry houdiniCommented:
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

=SUM(IF(Monday!$B$10:$B$509=$B8,IF(Monday!$A$10:$A$509=$A8,Monday!$E$10:$E$509,0),0))

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):INDEX(Monday!$A:$A,COUNTA(Monday!$A:$A))

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

regards, barry
0
 
barry houdiniCommented:
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 and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now