Sumif Formula - Won't Recognize Dates
 

I am trying to use a sumif and countif formula in conjuction with dates but
Excel (2003) will only recognize my formulas when I hard-code the date as
opposed to linking to a cell that contains the date. For some reason, when I
put the formula pointing to a cell (containing a date) - it returns 0, but
when I actually type in the date, I get a real number. Can Anyone help
please? Thanks very much


EX)

I am doing a range (2009), and this formula is returning the proper value:
"=SUMIF(Sheet1!$R$5:$R$223,"=1/1/2009",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"12/31/2009",Sheet1!M5:M224)"

However, for the same exact date range, this formula which attempts to point
to cells, does not work: "
=SUMIF(Sheet1!$R$5:$R$223,"A9",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"B9",Sheet1!M5:M224)

Any help is seriously appreciated

Sumif Formula - Won't Recognize Dates
 

Change this

"A9"

to this

""&A9


do the same change in the second part


"B9"


should be


""&B9



--


Regards,


Peo Sjoblom

"Dwolko" wrote in message
...
I am trying to use a sumif and countif formula in conjuction with dates but
Excel (2003) will only recognize my formulas when I hard-code the date as
opposed to linking to a cell that contains the date. For some reason, when
I
put the formula pointing to a cell (containing a date) - it returns 0, but
when I actually type in the date, I get a real number. Can Anyone help
please? Thanks very much


EX)

I am doing a range (2009), and this formula is returning the proper value:
"=SUMIF(Sheet1!$R$5:$R$223,"=1/1/2009",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"12/31/2009",Sheet1!M5:M224)"

However, for the same exact date range, this formula which attempts to
point
to cells, does not work: "
=SUMIF(Sheet1!$R$5:$R$223,"A9",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"B9",Sheet1!M5:M224)

Any help is seriously appreciated

Sumif Formula - Won't Recognize Dates
 

=SUMIF(Sheet1!$R$5:$R$223,"A9",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"B9",Sheet1!M5:M224)

In the above statement, you have this "A9" and this "B9"... anything
between quote marks is text to Excel (or VB for that matter)... so the A9
and B9 are not recognized as cell references. Take them out of the quote
marks and concatenate them with the greater than symbol (like this... ""&A9
and this... ""&B9) and the contents of A9 and B9 will be concatenated with
the greater than symbol and *that* will be used for the IF criteria of the
SUMIF functions. So, this is what your formula should look like...

=SUMIF(Sheet1!$R$5:$R$223,""&A9,Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,""&B9,Sheet1!M5:M224)

Rick


"Dwolko" wrote in message
...
I am trying to use a sumif and countif formula in conjuction with dates but
Excel (2003) will only recognize my formulas when I hard-code the date as
opposed to linking to a cell that contains the date. For some reason, when
I
put the formula pointing to a cell (containing a date) - it returns 0, but
when I actually type in the date, I get a real number. Can Anyone help
please? Thanks very much


EX)

I am doing a range (2009), and this formula is returning the proper value:
"=SUMIF(Sheet1!$R$5:$R$223,"=1/1/2009",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"12/31/2009",Sheet1!M5:M224)"

However, for the same exact date range, this formula which attempts to
point
to cells, does not work: "
=SUMIF(Sheet1!$R$5:$R$223,"A9",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"B9",Sheet1!M5:M224)

Any help is seriously appreciated

Sumif Formula - Won't Recognize Dates
 

Thanks so much guys. I really appreciate the help and explanation.

You really helped me out today!

-Dave
"Rick Rothstein (MVP - VB)" wrote:

=SUMIF(Sheet1!$R$5:$R$223,"A9",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"B9",Sheet1!M5:M224)

In the above statement, you have this "A9" and this "B9"... anything
between quote marks is text to Excel (or VB for that matter)... so the A9
and B9 are not recognized as cell references. Take them out of the quote
marks and concatenate them with the greater than symbol (like this... ""&A9
and this... ""&B9) and the contents of A9 and B9 will be concatenated with
the greater than symbol and *that* will be used for the IF criteria of the
SUMIF functions. So, this is what your formula should look like...

=SUMIF(Sheet1!$R$5:$R$223,""&A9,Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,""&B9,Sheet1!M5:M224)

Rick


"Dwolko" wrote in message
...
I am trying to use a sumif and countif formula in conjuction with dates but
Excel (2003) will only recognize my formulas when I hard-code the date as
opposed to linking to a cell that contains the date. For some reason, when
I
put the formula pointing to a cell (containing a date) - it returns 0, but
when I actually type in the date, I get a real number. Can Anyone help
please? Thanks very much


EX)

I am doing a range (2009), and this formula is returning the proper value:
"=SUMIF(Sheet1!$R$5:$R$223,"=1/1/2009",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"12/31/2009",Sheet1!M5:M224)"

However, for the same exact date range, this formula which attempts to
point
to cells, does not work: "
=SUMIF(Sheet1!$R$5:$R$223,"A9",Sheet1!M5:M224)-SUMIF(Sheet1!$R$5:$R$223,"B9",Sheet1!M5:M224)

Any help is seriously appreciated