CODEWITHSUNDEEP
excelexcel-formula
user9240523
user9240523

Reputation:

Excel count number of days by group

I have a table with employee ID and benefits receive date as below.

The first column is ID and the second column is the date

Employee ID Date
1   01/01/2013
1   01/10/2013
1   11/01/2013
2   03/01/2014
2   04/01/2014
2   05/10/2014
3   03/01/2016
3   04/01/2016
3   06/01/2016
3   08/01/2016

I need to find an automated way to calculate the number of days has each employee receive the benefit. For example, employee #1 is 305 days (11/1/2013 - 1/1/2013 + 1). The result should look like this:

Employee ID Days
1        305
2        71
3        154

I have tried to use "if" and "sumproduct" function discussed here: Simple Pivot Table to Count Unique Values and that does not get the answer I am looking for.

Upvotes: 0

Views: 354

Answers (1)

areklipno
areklipno

Reputation: 528

try:

=MAX(IF(A:A=E2;B:B;""))-MIN(IF(A:A=E2;B:B;""))+1


accept formula CTRL+SHIFT+ENTER

A - column with employee id
B - column with date
E2 - cell with employee id

Upvotes: 1

Related Questions