sumif — but with dates (Excel 2000)

Topic

New Reply

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 ( 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

Reply | Quote

Replies

In B2 enter this ARRAY formula (confirm with ctrl-shift-enter):

=SUM(IF(('Subject Log'!$A$2:$A$8=$A2)*(ISNUMBER('Subject Log'!C$2:C$8)),1))

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

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

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

Steve

Reply | Quote

Thanks, Steve, … that’s exactly what I needed!!!
Cindy

Reply | Quote

Thanks, Steve, … that’s exactly what I needed!!!
Cindy

Reply | Quote