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

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

On Jun 26, 4:50*pm, abis wrote:
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

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

"Pete_UK" wrote:

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

On Jun 26, 4:50 pm, abis wrote:
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

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.

"abis" wrote:

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

"Pete_UK" wrote:

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

On Jun 26, 4:50 pm, abis wrote:
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

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ted M H" wrote in message
...
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.

"abis" wrote:

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

"Pete_UK" wrote:

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

On Jun 26, 4:50 pm, abis wrote:
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

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" wrote:

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ted M H" wrote in message
...
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.

"abis" wrote:

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

"Pete_UK" wrote:

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

On Jun 26, 4:50 pm, abis wrote:
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

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ted M H" wrote in message
...
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" wrote:

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

--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Ted M H" wrote in message
...
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.

"abis" wrote:

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

"Pete_UK" wrote:

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

On Jun 26, 4:50 pm, abis wrote:
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

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.


"Ragdyer" wrote:

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ted M H" wrote in message
...
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" wrote:

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

--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Ted M H" wrote in message
...
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.

"abis" wrote:

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

"Pete_UK" wrote:

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

On Jun 26, 4:50 pm, abis wrote:
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

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

"abis" wrote:

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