formula logic

**kysaul** · 10-01-2008, 06:25 PM

My Worksheet contains rows of visits for tests, the result, recall interval for retesting and a cell to indicate if the person returned at the appropriate date. Each person will have multiple entries over time, the records may not be sorted.
I'm trying to create a formula for the ?Returned cell. It needs to test to see if there is a subsequent visit by that person, and if not flag the row for action. I created a list of unique names, with columns containing the row numbers of the matches, but can't get the logic for the test 'is there a subsequent visit'. Thanks

**mikerickson** · 10-01-2008, 06:47 PM

on your master sheet, with (duplicated) names in DA, date of visit in B, interval for Recall in C and "Made recall" in column D.

In D1 =SUMPRODUCT(--(A2:A100=A1),--(B2:B100<=B1+C1)) will count the number of times that the patient in A1 visited within the retest window (B1+C1). You may want to add a term to the SUMPRODUCT to test if the proper proceedure was performed in that visit(s).

**kysaul** · 10-01-2008, 07:10 PM

Thanks,
I have a column which calculates the total visits for each row,
=countif(NamedRange,A4)
where A4 is the beginning of the range, but I can't determine which of the visits any one row is from the total. In other words, the cell ?Returned needs to determine which visit it is, is there another subsequent visit, if so-okay, if not flag it.

**kysaul** · 10-01-2008, 08:04 PM

Sorry, I misunderstood your formula. When I set this up, I assumed the reference A2:A200 referred to the name column, but this produces a #Value error. What am I doing wrong?
About the criteria (B2:B100<=B1+C1)) am I right in thinking this counts if the value is anytime before the time B1+C1, not between B1 and B1+C1? Thanks

**mikerickson** · 10-03-2008, 09:19 AM

If you are testing A2:A200 and B2:B100, there will be an error, since SUMPRODUCT requires the arrays to be the same size.

**kysaul** · 10-03-2008, 12:47 PM

I solved the problem, the structure of the function that seems to work is
=sumproduct(($A$1:$A$100=$A1)*(($B$1:$B$100<=$B1+$C1)-
($B$1:B$100<$B1+1)))
which counts the number of visits since the index visit +1 day up 'til the recall time, C1.
Thanks for your help. being new to this, I didn't know of 'SumProduct'. It seems a usefull function.

=1