[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Unique count

hi,

I have 3 columns , column A with date and coulmn B with name of employee and column C with work type.
I want to know the number of days workded by each employee.
1-Feb      a
1-Feb      a
2-Feb      a
2-Feb      a
2-Feb      a
2-Feb      a
2-Feb      a
2-Feb      a
4-Feb      a
4-Feb      a
4-Feb      a
4-Feb      a
1-Feb      B
1-Feb      B
1-Feb      B
2-Feb      B
2-Feb      B
2-Feb      B
2-Feb      B
2-Feb      B
2-Feb      B
3-Feb      B
3-Feb      B
3-Feb      B
3-Feb      B
3-Feb      B


here a should give answer 3 as he has worked for 3 days (1st, 2nd and 4th of Feb)
similarly B should give answer 3 as he has worked for 3 dys.(1st, 2nd and 3rd of Feb)


 
0
VasuSidhu
Asked:
VasuSidhu
1 Solution
 
aikimarkCommented:
do you know how to create a pivot table?  That should be able to give you a breakdown of all employees and the days they worked.

How do you need to use this days-worked count? (in code, in formula, other)
0
 
krishnakrkcCommented:
Hi,

Here is a formula solution..

=SUM(IF(FREQUENCY(IF($B$2:$B$27=D2,IF($A$2:$A$27<>"",MATCH($A$2:$A$27,$A$2:$A$27,0))),ROW($A$2:$A$27)-ROW($A$2)+1),1))

where D2 holds "a"

It's an array formula. Confirmed with CTRL + SHIFT + ENTER.

0
 
VasuSidhuAuthor Commented:
Thanks a lot.
I really made my task easy.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now