Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
#5
|
|||
|
|||
Works, Thanks a lot!!
The challenge now would be actually implementing this code in VBA :D |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do count a cell range that include all dates prior to today? | Excel Discussion (Misc queries) | |||
Count Dates between Dates exclude Text | Excel Discussion (Misc queries) | |||
Making a Conditional Formula UNConditional | Excel Discussion (Misc queries) | |||
Unconditional formatting | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions |