How to count unique names (First Name Col A and Last Name Col B) with additional criteria in other columns

0
votes

Sheet 1 contains data entry from a user form. See data below.

 A              B               D              I
First Name      Last Name       Date           Type
Jane              Doe           1/15/15        Fall
Jane              Doe           1/24/15        Behavior
Jane              Doe           2/21/15        Fall
Angel             Wings         1/10/15        Fall

Sheet 2 calculates the data from sheet 1 and tracks and trends the information

  1. Count the number of unique names based on First and Last Name. I figured out the Array formula for this one which is:

=SUM(IF(FREQUENCY(IF('Event Tracker'!A5:A1502&'Event Tracker'!B5:B1502<>"",MATCH('Event Tracker'!A5:A1502&"@"&'Event Tracker'!B5:B1502,'Event Tracker'!A5:A1502&"@"&'Event Tracker'!B5:B1502,0)),ROW('Event Tracker'!A5:A1502)-ROW(A5)+1)>0,1))

Here is where I really need help - I have tried every variation I can think of.

2.Count the number of unique patients (Col. A + Col B) with an event occurring between a specified date based on date in col. D (>=1/1/15 and <=1/31/15)?

  1. How many unique patient names (Col A + Col B) had a Fall (Col I)?

  2. How many unique patient names (Col A + Col B) had a Fall (Col I) between >=1/1/15 and <=1/31/15 based on the date in column D?

1
excelexcel-formulaarray-formulas

1 Answers

0
votes

You can add additional conditions at the start of the FREQUENCY function, e.g. for how many different patients had a fall, use this:

=SUM(IF(FREQUENCY(IF('Event Tracker'!I5:I1502="Fall",IF('Event Tracker'!A5:A1502&'Event Tracker'!B5:B1502<>"",MATCH('Event Tracker'!A5:A1502&"@"&'Event Tracker'!B5:B1502,'Event Tracker'!A5:A1502&"@"&'Event Tracker'!B5:B1502,0))),ROW('Event Tracker'!A5:A1502)-ROW(A5)+1)>0,1))

To add extra conditions you can just keep adding IFs in the same place......but don't forget to add the appropriate parentheses.

For between two dates the syntax is as follows:

=SUM(IF(FREQUENCY(IF('Event Tracker'!D5:D1502>="1/1/15"+0,IF('Event Tracker'!D5:D1502<="1/31/15"+0,IF('Event Tracker'!A5:A1502&'Event Tracker'!B5:B1502<>"",MATCH('Event Tracker'!A5:A1502&"@"&'Event Tracker'!B5:B1502,'Event Tracker'!A5:A1502&"@"&'Event Tracker'!B5:B1502,0)))),ROW('Event Tracker'!A5:A1502)-ROW(A5)+1)>0,1))