:suspicious: 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 ??
sum if looking in wrong cells
- Thread starter PM BMG
- Start date
- Joined
- Mar 13, 2011
- Messages
- 2,531
- Reaction score
- 6
- Points
- 38
- Location
- Nanaimo, BC, Canada
- Website
- www.excelguru.ca
- Excel Version(s)
- 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.
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
=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
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......