SUMIF Range

[abis]

Can anyone tell me why when specifying the range in a SUMIF function it
returns different figures if using a column reference (e.g. C:C) and a data
reference (e.g. C2:C34) if C34 is the last cell in the column to contain any
data?

Thanks

 

[Pete_UK]

Click in C35, press <End> once followed by <down-arrow> - does this
take you to the bottom row (65536), or to some other cell which might
be adding to your SUMIF value?

Also, what do you have in C1?

Pete

 

[abis]

Hi Pete, Thanks I have tried that - it takes me to row 65536. In cell C1
there is a title row containing the word 'Location'
Thanks
Abi

 

[Ted M H]

It's because when you select the entire column it's including the word
Location as part of the calculation. If you do the formula as C1:C34 you'll
get the same result as C:C--both wrong. The sum range should exclude any
cells that aren't going to be used in the calculation.

 

[Ragdyer]

<<<"The sum range should exclude any cells that aren't going to be used in
the calculation.">>>

That's *not* really true!

The reason to use Sumif() in the first place is to have the function itself
*exclude* cells from the sum range that don't meet with a criteria.
Sumif() works with errors, logicals and zero length strings in the sum
range, *IF* the sum range is also the criteria range.

We need to see the exact Sumif formula and to know exactly what data is in
the column(s) referenced by the formula.

 

[Ted M H]

Well you don't really have to see the exact formula to see this problem. Do
this little test:

A B C
Location
1 Hot 555
2 Hot 666
3 Cold 777
4 Warm 888
5

Put these formulas in C6, C7 and C8:
=SUMIF(B2:B5,"Hot",C1:C5)
=SUMIF(B2:B5,"Hot",C:C)
=SUMIF(B2:B5,"Hot",C1:C7)
All return the same incorrect answer.
Put this is C9:
=SUMIF(B2:B5,"Hot",C2:C5)
This returns the correct answer.
You need to exclude C1 from the Sum range. It also works if you expand the
criteria range to include B1.
How about this: the sum range should exclude any rows that aren't also used
in the criteria range part of the calculation.
I'm assuming there are two ranges of data, even though abis doesn't say
this. If you do the same test with a single range for both Criteria and Sum
the problem doesn't occur.

 

[Ragdyer]

My comment pertains to the *correct* usage of the function!

Your first 3 examples are *wrong* - they are not in the correct syntax for
the function.

The assumption is that the OP, and you ... use the function as it was
designed to be used.

That's why, in most cases, the OP is asked to post their formula ... so that
the problem can be better understood, and to insure that the OP *is* using
the correct syntax.

 

[Ted M H]

That's sort of what I said in the first place. Whatever. Sounds like you're
the expert here, so I'll bow out of this discussion.

 

[Rick]

I must admit I use "C:C" all the time and don't have a problem.

As Ragdyer states the syntax must be correct. when writing the formula you
must use the same throughout, e.g

=sumif(a2:a34, b2,c2:c4) or =sumif(a:a, b2,c:c) not =sumif(a:a, b2,c2:c4).

As ted showed if the rows don't match the formula will always provide an
incorrect answer.

Hope one of us has helped.

Rick