Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sumif -- but with dates (Excel 2000)

    Hi All, again any help would be greatly appreciated ...
    I have a Subject Log data file that contains a sheet with an InvestigatorID column (A); a SubjectID column ([img]/forums/images/smilies/cool.gif[/img] and columns providing dates for visit 1 through visit 5 (G). Every subject is unique but each Investigator can have multiple subjects. I cannot modify this data sheet. In another sheet (Status), I want to list all the Investigator IDs in one column and provide a count of how many subjects have gone through each visit.
    I do not want to do this manually. Is there a formula I could use?
    I have attached an example in case I wasn't clear.
    Thanks,
    --cat

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sumif -- but with dates (Excel 2000)

    In B2 enter this ARRAY formula (confirm with ctrl-shift-enter):
    <pre>=SUM(IF(('Subject Log'!$A$2:$A$8=$A2)*(ISNUMBER('Subject Log'!C$2:C$8)),1))</pre>


    Copy B2 to B3:B5. Then Copy B2:B5 to C3:F5.

    <pre>=SUMPRODUCT(('Subject Log'!$A$2:$A$8=$A2)*1,(ISNUMBER('Subject Log'!C$2:C$8))*1)</pre>


    is a "regular" formula that will work. Copy from B2 to B2:F5

    Steve

  3. #3
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif -- but with dates (Excel 2000)

    Thanks, Steve, ... that's exactly what I needed!!!
    Cindy

Posting Permissions

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