# How to count name appearence but only once per given dates

#### Miro H

##### New Member
Hi all,

This is my first post, hope someone can help me.

I have inherited an excel sheet that tracks the activities on site, by date and personnel. It looks something like this.

 Date Site Person 21/4/2015 1 A, B, C 21/4/2015 2 D, E 22/4/2015 2 A, B, C 22/4/2015 3 D, E, F 22/4/2015 4 A, C, F 22/4/2015 5 B, E, F 23/4/2015 5 A, B 23/4/2015 6 A

<tbody>
</tbody>

I have to do the report how many days each person spend on sites (out of office).

Based on the table above this would be the result:

 Person Days A 3 B 3 C 2 D 2 E 2 F 1

<tbody>
</tbody>

I've tried with COUNTIF, but simple COUNTIF function would not help as I need to count each person only once per given date. For example person A appears 5 times in total, but only on 3 different days. Or Person F appears 3 times, but only on 1 day so it was 1 day out of office.
So I'm stuck now.

One more thing, I can't change the appearance of original excel sheet.

Could anyone suggest how to make a formula that would automatically count this?

#### JoeMo

##### MrExcel MVP
Assuming that all the sites you list are "offsite" sites, the site is not relevant only the first and third columns are relevant to what you want to calculate. Here's a UDF that you can use to do that. It's used just like any worksheet function as in the example below (copy formula in G2 down to cover all persons).
Excel Workbook
ABCDEFG
1DateSitePersonPersonDays Offsite
221/4/20151A, B, CA
321/4/20152D, EB3
422/4/20152A, B, CC2
522/4/20153D, E, FD2
622/4/20154A, C, FE2
722/4/20155B, E, FF1
823/4/20155A, B
923/4/20156A
 Sheet6

To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
Code:
``````Function DaysOffSite(S As String, Dtes As Range) As Variant
Dim d As Object, Ct As Long, c As Range, i As Long
Set d = CreateObject("Scripting.dictionary")
For Each c In Dtes
If InStr(c.Offset(0, 2).Value, S) > 0 Then
If Not d.exists(c.Value) Then
Ct = Ct + 1
End If
End If
Next c
DaysOffSite = Ct
End Function``````

#### Domenic

##### MrExcel MVP
Are the dates true date values? If so, try...

A1:F9

 Date Site Person Person Days 4/21/15 1 A, B, C A 3 4/21/15 2 D, E B 3 4/22/15 2 A, B, C C 2 4/22/15 3 D, E, F D 2 4/22/15 4 A, C, F E 2 4/22/15 5 B, E, F F 1 4/23/15 5 A, B 4/23/15 6 A

<tbody>
</tbody>

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(", "&E2&",",", "&\$C\$2:\$C\$9&",")),IF(ISNUMBER(\$A\$2:\$A\$9),\$A\$2:\$A\$9)),\$A\$2:\$A\$9)>0,1))

Hope this helps!

#### lrobbo314

##### Well-known Member
Nice Domenic. I was trying to figure out a formula solution to this but couldn't quite get there. I was using Transpose and countif, but I couldn't figure it out. Frequency is still one that I need to look into more. Cool solution.

#### Domenic

##### MrExcel MVP
Nice Domenic. I was trying to figure out a formula solution to this but couldn't quite get there. I was using Transpose and countif, but I couldn't figure it out. Frequency is still one that I need to look into more. Cool solution.
Thanks! Much appreciated!

Cheers!

1,081,849
Messages
5,361,681
Members
400,645
Latest member
Zeak

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...