SUMIF CRITERIA PROBLEM

[GEORGE HUMPHRIES]

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

 

[Don]

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]

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

 

[Pete_UK]

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

 

[Lorcan Dene]

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.

 

[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

 

[Dave Peterson]

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

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

 

[Dave Peterson]

Watch out for that typo in the second example.

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

 

[GEORGE HUMPHRIES]

That's cured it thank you.

I didn't realise that to use the less than symbol < with a cell number it
needed to be in quotes with an & sign before the cell number.

George