Can anyone help me please.

I have a problem with the criteria part of sumif using cell values.

I can type a value into a criteria directly in for example 2007, and it
works.

e.g. SUMIF(YEAR,2007,VALUE)

Where year and value are ranges.

But when I refer to a cell such as B12 which contains the value 2007, the
criteria doesn't work.

SUMIF(YEAR,B12,VALUE)

Does the cell reference need to be MARKED in any way?

George Humphries

is it because the 2007 is a date? try and copy / past special to another
cell for each of these and make sure the format and data are the same. That
is what I had an issue with before.

"GEORGE HUMPHRIES" wrote:

Can anyone help me please.

I have a problem with the criteria part of sumif using cell values.

I can type a value into a criteria directly in for example 2007, and it
works.

e.g. SUMIF(YEAR,2007,VALUE)

Where year and value are ranges.

But when I refer to a cell such as B12 which contains the value 2007, the
criteria doesn't work.

SUMIF(YEAR,B12,VALUE)

Does the cell reference need to be MARKED in any way?

George Humphries

They started life as a date, but I used the year(date) function to derive
the value of the cells I am referring to.

e.g.

date in b1 = 22/01/07, value of cell d1 = year(b1)

SUMIF(YEAR,D1,VALUE)

I have also tried typing in the cell value as a number before referring to
it but it doesn't seem to work.

George


"Don" wrote in message
...
is it because the 2007 is a date? try and copy / past special to another
cell for each of these and make sure the format and data are the same.
That
is what I had an issue with before.

"GEORGE HUMPHRIES" wrote:

Can anyone help me please.

I have a problem with the criteria part of sumif using cell values.

I can type a value into a criteria directly in for example 2007, and it
works.

e.g. SUMIF(YEAR,2007,VALUE)

Where year and value are ranges.

But when I refer to a cell such as B12 which contains the value 2007, the
criteria doesn't work.

SUMIF(YEAR,B12,VALUE)

Does the cell reference need to be MARKED in any way?

George Humphries

YEAR and VALUE are reserved words in Excel because they are the names
of functions. Change your names to something else and try it with the
new names.

Hope this helps.

Pete

On Jan 22, 12:55*am, "GEORGE HUMPHRIES"
wrote:
They started life as a date, but I used the year(date) function to derive
the value of the cells I am referring to.

e.g.

date in b1 = 22/01/07, value of cell d1 = year(b1)

SUMIF(YEAR,D1,VALUE)

I have also tried typing in the cell value as a number before referring to
it but it doesn't seem to work.

George

"Don" wrote in message

...



is it because the 2007 is a date? *try and copy / past special to another
cell for each of these and make sure the format and data are the same.
That
is what I had an issue with before.

"GEORGE HUMPHRIES" wrote:

Can anyone help me please.

I have a problem with the criteria part of sumif using cell values.

I can type a value into a criteria directly in for example 2007, and it
works.

e.g. SUMIF(YEAR,2007,VALUE)

Where year and value are ranges.

But when I refer to a cell such as B12 which contains the value 2007, the
criteria doesn't work.

SUMIF(YEAR,B12,VALUE)

Does the cell reference need to be MARKED in any way?

George Humphries- Hide quoted text -

- Show quoted text -

2007 wont be a date, unless yo tell it, its a date (via the format menu).

When you use the cell ref, are you getting any kind of an answer? or a #value?

I've just tried what you have, and it works fine, bit of a strange one really.


"Don" wrote:

is it because the 2007 is a date? try and copy / past special to another
cell for each of these and make sure the format and data are the same. That
is what I had an issue with before.

"GEORGE HUMPHRIES" wrote:

Can anyone help me please.

I have a problem with the criteria part of sumif using cell values.

I can type a value into a criteria directly in for example 2007, and it
works.

e.g. SUMIF(YEAR,2007,VALUE)

Where year and value are ranges.

But when I refer to a cell such as B12 which contains the value 2007, the
criteria doesn't work.

SUMIF(YEAR,B12,VALUE)

Does the cell reference need to be MARKED in any way?

George Humphries

Sorry,

By mistake I simplified the example, it should have read

SUMIF(YEAR,"<B12",VALUE)

Using just the cell reference it works, but when I try to set it to less
than the cell reference it doesn't.

George


"Lorcan Dene" wrote in message
...
2007 wont be a date, unless yo tell it, its a date (via the format menu).

When you use the cell ref, are you getting any kind of an answer? or a
#value?

I've just tried what you have, and it works fine, bit of a strange one
really.


"Don" wrote:

is it because the 2007 is a date? try and copy / past special to another
cell for each of these and make sure the format and data are the same.
That
is what I had an issue with before.

"GEORGE HUMPHRIES" wrote:

Can anyone help me please.

I have a problem with the criteria part of sumif using cell values.

I can type a value into a criteria directly in for example 2007, and it
works.

e.g. SUMIF(YEAR,2007,VALUE)

Where year and value are ranges.

But when I refer to a cell such as B12 which contains the value 2007,
the
criteria doesn't work.

SUMIF(YEAR,B12,VALUE)

Does the cell reference need to be MARKED in any way?

George Humphries

Try:
=sumif(year,"<"&b12,value)

If you wanted to put the date into formula directly:
=sumif(year,"<"&date(2007,12,25),valuel)
(for example)


GEORGE HUMPHRIES wrote:

Sorry,

By mistake I simplified the example, it should have read

SUMIF(YEAR,"<B12",VALUE)

Using just the cell reference it works, but when I try to set it to less
than the cell reference it doesn't.

George

"Lorcan Dene" wrote in message
...
2007 wont be a date, unless yo tell it, its a date (via the format menu).

When you use the cell ref, are you getting any kind of an answer? or a
#value?

I've just tried what you have, and it works fine, bit of a strange one
really.


"Don" wrote:

is it because the 2007 is a date? try and copy / past special to another
cell for each of these and make sure the format and data are the same.
That
is what I had an issue with before.

"GEORGE HUMPHRIES" wrote:

Can anyone help me please.

I have a problem with the criteria part of sumif using cell values.

I can type a value into a criteria directly in for example 2007, and it
works.

e.g. SUMIF(YEAR,2007,VALUE)

Where year and value are ranges.

But when I refer to a cell such as B12 which contains the value 2007,
the
criteria doesn't work.

SUMIF(YEAR,B12,VALUE)

Does the cell reference need to be MARKED in any way?

George Humphries




--

Dave Peterson

Watch out for that typo in the second example.

=sumif(year,"<"&date(2007,12,25),value)

Dave Peterson wrote:

Try:
=sumif(year,"<"&b12,value)

If you wanted to put the date into formula directly:
=sumif(year,"<"&date(2007,12,25),valuel)
(for example)

GEORGE HUMPHRIES wrote:

Sorry,

By mistake I simplified the example, it should have read

SUMIF(YEAR,"<B12",VALUE)

Using just the cell reference it works, but when I try to set it to less
than the cell reference it doesn't.

George

"Lorcan Dene" wrote in message
...
2007 wont be a date, unless yo tell it, its a date (via the format menu).

When you use the cell ref, are you getting any kind of an answer? or a
#value?

I've just tried what you have, and it works fine, bit of a strange one
really.


"Don" wrote:

is it because the 2007 is a date? try and copy / past special to another
cell for each of these and make sure the format and data are the same.
That
is what I had an issue with before.

"GEORGE HUMPHRIES" wrote:

Can anyone help me please.

I have a problem with the criteria part of sumif using cell values.

I can type a value into a criteria directly in for example 2007, and it
works.

e.g. SUMIF(YEAR,2007,VALUE)

Where year and value are ranges.

But when I refer to a cell such as B12 which contains the value 2007,
the
criteria doesn't work.

SUMIF(YEAR,B12,VALUE)

Does the cell reference need to be MARKED in any way?

George Humphries




--

Dave Peterson

--

Dave Peterson