Hi I 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 10

2 Selling 2 2 2

3 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

I think the date column might be throwing it off. do you have to sum the whole column like b:b?

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 1Then your formula, =SUMIF($A$1:$D$3,$A1,B:B) with the corrected B:B returns 10, as expected.

MIKE

If you need to post an example of your worksheet use the Pre Tags

Read this:http://www.computing.net/howtos/sho...

MIKE

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

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 1and 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.

AND

Your Criteria to be matched is $A$1 which is the

Column headingCategoryIf I use the above layout of data, then the formula returns the

Date Headingin Column B, which is 40461.To return the totals for the Category

Productionthe 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

Sellingthe 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

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History