Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    countif with dates (2000)

    I need a formula that will count the number of times the word Dennis was typed per each day. A1:A12311 contain names and B1:B12311 have dates of the month. Right now I have a formula on sheet one (=countif($A$4:$A$12311,D1) in D1 I have the name Dennis. This works good but it does not break it down by days. I thought on sheet 2 the headers could be something like Dates and Results with the Results having a formula to count how many times Dennis was typed on each day

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: countif with dates (2000)

    Say you enter Jan-2006 in F1 and fill down to Aug-2006 (or Dec-2006 or whatever).
    In G1, enter this array formula (confirm with Ctrl+Shift+Enter):

    =SUM(($A$1:$A$12311=$D$1)*(DATE(YEAR($B$1:$B$12311 ),MONTH($B$1:$B$12311),1)=F1))

    Fill down as far as needed.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It would be nice if you provided some feedback to the replies you receive. Otherwise, other Loungers reading threads you started have no idea whether the replies were helpful. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •