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.
<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:
<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?
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?