COUNT IF with unconditional AND on cell dates
 

I have a slight problem here any input would be appreciated.

Trying to gather a data that has 2 conditions where both of them have to be true.
1st condition Name
2nd conditionam Date

Here is the code of what I haves so far.
=COUNT(IF(Requests!H:H,"<="&DATE(2012,4,31),IF(Req uests!D:D,"Marcus Comins")))

Ironically enough trying to set this up in VBA however need to get the formula to fucntion in the first place.

Thanks in advance

Aram

Temporary fix which is better than anything else.

=COUNT(IF(H:H=T36, IF(H:H<=T37, COUNTIF(D:D, "Name"))))

Only disadvantage here is that the dates were defined in different columns,
hence the reference to T36 and T37.

For programmers this could be a real hassle especially if youre trying to define every single date parameter.

COUNT IF with unconditional AND on cell dates
 

hi Aram,

=SUMPRODUCT(--(Requests!H1:H65535,"<="&DATE(2012,4,31))*(Request s!D1:D65535="Marcus Comins"))


--
isabelle



Le 2012-04-13 11:00, Aramazd a écrit :
I have a slight problem here any input would be appreciated.

Trying to gather a data that has 2 conditions where both of them have to
be true.
1st condition Name
2nd conditionam Date

Here is the code of what I haves so far.
=COUNT(IF(Requests!H:H,"<="&DATE(2012,4,31),IF(Req uests!D:D,"Marcus
Comins")))

Ironically enough trying to set this up in VBA however need to get the
formula to fucntion in the first place.

Thanks in advance

Aram

COUNT IF with unconditional AND on cell dates
 

sorry,

=SUMPRODUCT(--(Requests!H1:H65535<=&DATE(2012,4,31))*(Requests!D 1:D65535="Marcus Comins"))

--
isabelle


Le 2012-04-13 16:37, isabelle a écrit :
=SUMPRODUCT(--(Requests!H1:H65535,"<="&DATE(2012,4,31))*(Request s!D1:D65535="Marcus Comins"))

Works, Thanks a lot!!

The challenge now would be actually implementing this code in VBA :D

COUNT IF with unconditional AND on cell dates
 

hi,

ActiveCell.Formula = "=SUMPRODUCT(--(Requests!H1:H65535<=DATE(2012,4,31))*(Requests!D1 :D65535=""Marcus Comins""))"

or

Dim dt As Long, rng1 As Range, rng2 As Range
dt = DateSerial(2012, 4, 30)
Set rng1 = Range("H1:H65535")
Set rng2 = Range("D1:D65535")
ActiveCell = Evaluate("SUMPRODUCT((Requests!" & rng1.Address & "<=" & dt & ")*(Requests!" & rng2.Address & "=""Marcus Comins""))")


--
isabelle



Le 2012-04-16 05:18, Aramazd a écrit :
Works, Thanks a lot!!

The challenge now would be actually implementing this code in VBA :D