# Sum if is double counting

Microsoft Office excel 2007 - upgrade
November 21, 2010 at 13:34:42
Specs: Windows XP
 HiI have never come across this before and cannot work it out.I have a range of data: A B C D Category Oct-10 Nov-10 Dec-10 1 Production 10 10 102 Selling 2 2 23 Markeeting 1 1 1When I do a sumif as follows =sumif(\$a\$1:\$d\$3,\$a1,B;B) so that the range is absolute, the criteria is always from column a and the sum column moves across one each time I am getting a double count. Eg Marketing is returning a value of 2 where I think should be 1.If I delete the values in column C the calculation returns 1.Any ideas?Thanks in advance.Nick

See More: Sum if is double counting

#1
November 21, 2010 at 14:02:05
 I think the date column might be throwing it off. do you have to sum the whole column like b:b?

Report •

#2
November 21, 2010 at 14:23:34
 I'm not sure how your data is set up, but on first glance your formula has an error:B;B

Report •

#3
November 21, 2010 at 14:51:52
 If you need to post an example of your worksheet use the Pre Tags Read this:http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
November 22, 2010 at 01:01:17
 HiAllThe ; was a typo in my post. I had it right in the formula.But also if I make the sum range absolute (ie exclude the date at the top) is still giving the error.

Report •

#5
November 22, 2010 at 03:57:18
 If your data looks like this:``` A B C D 1) Category 10-Oct 10-Nov 10-Dec 2) Production 10 10 10 3) Selling 2 2 2 4) Markeeting 1 1 1 ```and your using this formula:=SUMIF(\$A\$1:\$D\$3,\$A1,B:B)The structure of the =SUMIF() function is:=SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal)Your Range of things to be examined is \$A\$1 through \$D\$3 which leaves you one row short of what you will need if you intend to sum all of your categories, it should be A1 through D4.ANDYour Criteria to be matched is \$A\$1 which is the Column heading CategoryIf I use the above layout of data, then the formula returns the Date Heading in Column B, which is 40461.To return the totals for the Category Production the formula should be changed to:=SUMIF(\$A\$1:\$D\$4,\$A2,B:B)This will return the number 10 from Column B, Cell 2.To return the totals for the Category Selling the formula should be changed to:=SUMIF(\$A\$1:\$D\$4,\$A3,B:B)I would advise against using B:B, the whole B column as a range to total.If sometime in the future a spurious figure gets added to column B it will return a false total.Better off using the same rows as in your Range of things to be examined, IE B1:B4EDIT:Make sure the cell where you place the formula is formatted correctly.MIKE

Report •