Hi there.

I have an array formula (Sum(IF((...)) that I would like to include a date
value in. For example, Sum(If(($C$1:C$1000=1/3/2005),$B$1:$B$1000,0)).

For some reason that I can't figure out, it seems to work when I do this
directly in the worksheet but will not work when I try to create the formula
in VBA.

Is there some special way of handling a date value?

Thanks,
CaroKannDefence

this works for me when I array enter it:
=SUM(IF(($C$1:C$1000=DATEVALUE("1/3/2005")),$B$1:$B$1000,0))

but your formula doesn't

Also, it would be easier to use sumif which will work like you have and
doesn't require to be array entered

=Sumif($C$1:C$1000,"1/3/2005",$B$1:$B$1000)

creating the formula in VBA is not clear.

Are you trying to get the sum in VBA or are you trying to write the formula
itself to a cell in VBA?


ActiveCell.Formula =
"=SUM(IF(($C$1:C$1000=DATEVALUE(""1/3/2005"")),$B$1:$B$1000,0))"

or
ActiveCell.Formula = "=Sumif($C$1:C$1000,""1/3/2005"",$B$1:$B$1000)"

or for the sum only:

ActiveCell.Formula = Application.Sumif(Range("$C$1:C$1000"), _
"1/3/2005",Range("$B$1:$B$1000"))

--
Regards,
Tom Ogilvy


.. "Caro-Kann Defence" wrote in
message ...
Hi there.

I have an array formula (Sum(IF((...)) that I would like to include a date
value in. For example, Sum(If(($C$1:C$1000=1/3/2005),$B$1:$B$1000,0)).

For some reason that I can't figure out, it seems to work when I do this
directly in the worksheet but will not work when I try to create the
formula
in VBA.

Is there some special way of handling a date value?

Thanks,
CaroKannDefence

Tom.

Thanks for the reply. Your formula in the second line seems to have done
the trick! I think I may not have been including the quotation marks
properly.


"Tom Ogilvy" wrote:

this works for me when I array enter it:
=SUM(IF(($C$1:C$1000=DATEVALUE("1/3/2005")),$B$1:$B$1000,0))

but your formula doesn't

Also, it would be easier to use sumif which will work like you have and
doesn't require to be array entered

=Sumif($C$1:C$1000,"1/3/2005",$B$1:$B$1000)

creating the formula in VBA is not clear.

Are you trying to get the sum in VBA or are you trying to write the formula
itself to a cell in VBA?


ActiveCell.Formula =
"=SUM(IF(($C$1:C$1000=DATEVALUE(""1/3/2005"")),$B$1:$B$1000,0))"

or
ActiveCell.Formula = "=Sumif($C$1:C$1000,""1/3/2005"",$B$1:$B$1000)"

or for the sum only:

ActiveCell.Formula = Application.Sumif(Range("$C$1:C$1000"), _
"1/3/2005",Range("$B$1:$B$1000"))

--
Regards,
Tom Ogilvy


.. "Caro-Kann Defence" wrote in
message ...
Hi there.

I have an array formula (Sum(IF((...)) that I would like to include a date
value in. For example, Sum(If(($C$1:C$1000=1/3/2005),$B$1:$B$1000,0)).

For some reason that I can't figure out, it seems to work when I do this
directly in the worksheet but will not work when I try to create the
formula
in VBA.

Is there some special way of handling a date value?

Thanks,
CaroKannDefence