PDA

The_excell_initiate
2017-04-05, 12:54 PM
Hey Guys,

Was hoping someone could help with this. I have a set of data somewhat similar to this:

Open Date
Close date
Item
Owner

01/01/2017
05/02/2017
Item 1
Peter

05/02/2017
07/02/2017
Item 2
John

05/02/2017
15/02/2017
Item 3
Peter

24/01/2017
15/03/2017
Item 4
John

What I am trying to achieve is a pivot table that shows the following

John
Peter

January
Count of items
Count of items

February
Count of items
Count of items

March
Count of items
Count of items

So what I need is a way for excel to identify all the months it was active , for example item 4 was active in January, February and March. So I would need it to report under all three months.
I know I can do this by splitting them onto individual lines and adding another column, but i do expect excel is smarter than this. :D

Hope someone can help.

p.s. running version 2013

p45cal
2017-04-10, 11:44 PM
See attached.
I'd have difficulty getting a pivot table to do this, so formula instead.
Formula in H5 is array-entered (Ctrl+Shift+Enter, not just Enter):
=SUM(((IF((\$B\$2:\$B\$5<EDATE(\$G5,1)),\$B\$2:\$B\$5,EDATE(\$G5,1)))>(IF(\$A\$2:\$A\$5>\$G5,\$A\$2:\$A\$5,\$G5)))*(\$D\$2:\$D\$5=H\$4))
and copied down and across.
I feel there must be a more elegant formula.