Results 1 to 4 of 4

Thread: sum if looking in wrong cells

  1. #1

    sum if looking in wrong cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Help my sum if formula is looking in the wrong cells. The formula looks correct and when I check it it says it's looking in the correct range - BUT it's summing values from another range - anyone have this problem ??

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,233
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Post the formula, as well as what you think it should be doing. (i.e. which cells are you looking at, for what, and which cells do you want to SUM.) We'll make sure you've got everything in the right place.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi there thanks for responding. It's a simple enough formula. I'm assigning tasks to people and want to sum the amount of time they are allocated in any one day in order to ensure I'm not overbooking them. Here is the formula.

    =SUMIF($H$4:$W$461,$G465,AD$4:AD$461)

    The H to W - references the area where I assign by a persons initials - there are 16 in total
    The G reference - refers to the cell where an individuals initials are e.g. NP
    the AD to AD - refers to the column with all items assigned on a date e.g Jan 24th

    So I'm asking the formula each time you see an instance e.g. NP ($G465) in $H$4:$W$461 sum all numbers in AD$4:AD$461.

    I have therefore 16 rows counting assigned time for each person for each day. The first row seems to be working however the subsequent ones are acting strangely for example if I'm in Cell AA468 and the formula is =SUMIF($H$4:$W$461,$G468,AA$4:AA$461) it is actually counting entries from future columns e.g. AB or AD - this varies through sometimes a column ahead sometimes several.

    I'm wondering if this is a result of a drag and drop at some point. But given the amount of data I have it would be impossible to find. Any help greatly appreciated. Apologies for the long answer

  4. #4
    Hello again - I've solved the problem by simplifying the reference area of $H$4:$W$461 - I've narrowed this area to one column (each person is assigned under one column only) and it is now working. Many thanks just knowing someone out there might be able to help was enough to get me on the path......


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •