Sum if is double counting

Microsoft Office excel 2007 - upgrade
November 21, 2010 at 13:34:42
Specs: Windows XP

I have never come across this before and cannot work it out.

I have a range of data:
Category Oct-10 Nov-10 Dec-10
1 Production 10 10 10
2 Selling 2 2 2
3 Markeeting 1 1 1

When 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.


See More: Sum if is double counting

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 •

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 <semi-colon

should be

B:B <colon

If your data is:

           A     B       C       D
1) Production	10	10	10
2) Selling	 2	 2	 2
3) Markeeting	 1	 1	 1

Then your formula, =SUMIF($A$1:$D$3,$A1,B:B) with the corrected B:B returns 10, as expected.


Report •

November 21, 2010 at 14:51:52
If you need to post an example of your worksheet use the Pre Tags
Read this:


Report •

Related Solutions

November 22, 2010 at 01:01:17

The ; 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 •

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:


The structure of the =SUMIF() function is:


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.


Your Criteria to be matched is $A$1 which is the Column heading Category

If 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:


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:


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:B4


Make sure the cell where you place the formula is formatted correctly.


Report •

Ask Question