SUMIF Formula not working

RIP2632

New Member
Joined
Oct 15, 2005
Messages
11
I have a SUMIF formula that is not bringing back the correct data. Here is one of the formulas, =SUMIF($C$2:$T$43,"704",N$2:N$43). The range is the same for all of the formulas but the criteria changes as well as the sum range. There are 4 columns (sum ranges) that I am trying to sum. The first one worked correctly and the last one worked correctly, but the two in the middle do not bring back the correct data.

Any help would be appreciated.

Cameron
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
With SUMIF the sum range must be the same size as the criteria range. If you don't specify it that way then Excel forces it anyway. Your formula is the same as

=SUMIF($C$2:$T$43,"704",N$2:AE$43)

i.e. if only T43 in your criteria range is equal to "704" then it'll return the value from AE43.
 

RIP2632

New Member
Joined
Oct 15, 2005
Messages
11

ADVERTISEMENT

I would like the formula to retrun the sum of all cells in column "N" that have "704" in the same cell in column "C". I have double checked the calculation and it grabbing extra data as it is not the correct figure.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
=SUMIF($C$2:$C$43,"704",N$2:N$43)

If you have numbers, not text, in column C then you shouldn't use the quotes
 

RIP2632

New Member
Joined
Oct 15, 2005
Messages
11

ADVERTISEMENT

it does not change the outcome of this particular formula.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Have you tried without the quotes?

=SUMIF($C$2:$C$43,704,N$2:N$43)

What result do you get, zero, incorrect sum or an error?
 

RIP2632

New Member
Joined
Oct 15, 2005
Messages
11
I get the same as with the quotes, I have also referenced a cell and still the same answer.
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:

You might try verifying that the items in column N are numbers and not text.

plettieri
 

Watch MrExcel Video

Forum statistics

Threads
1,114,061
Messages
5,545,763
Members
410,704
Latest member
Cobber2008
Top