Complex problem. Is this possible?

Hello everyone,

I have a rather complex problem. I am dealing with a spreadsheet that looks like the following, except much much larger (names are fictional) :
Col.A Col.K
Name Manager
Bill Will
Joe Sara
Frank Sara
Joe Sara
David Will
David Wll
Frank Sara

The first thing I needed was to count how many employees there were in total, making sure that names were not double counted (there is only 1 Bill, 1 Joe, 1 Frank and 1 David). The only way i could find to do this was:
=SUMPRODUCT((sheet1!A2:A499<>"")/COUNTIF(sheet1!A2:A499, sheet1!A2:A499&""))

Now what I need to do is adapt this formula so that it still counts names of employees once but also only counts employees under a certain manger. For example, i need a formula that will tell me Sara has 2 employees (Joe & Frank) and not 4 (2 for Joe and 2 for Frank).

I hope that makes sense to someone

maybe something like...

=SUM(IF(FREQUENCY(IF(A2:A8<>"",IF(B2:B8=D2,MATCH(A2:A8,A2:A8,0))),ROW(A2:A8)-ROW(A2)+1),1)) control shift enter

View attachment DCunningham.xlsx

Hi, thanks for the reply and apologies for my delay in gettnig back to you

I think i can understand the idea of this formula but i cant see the attachment, it opens/saeves as a .php and I can't open it. Can you reattach somehow? and/or explain the formula?

Thanks

try downloading it from google drive

https://drive.google.com/file/d/0B1sfV1hO1M23MWIwRUx6S2Z6WUk/view?usp=sharing

I see now, that seems to be working, thanks so much.
if you can do one more thing for me you'd be a hero!
Imagine there is a 3rd coloumn that has each employee's start date, ie 06/02/2014, 25/7/2015 etc...

Is there a way that I can count only those employees who started from a certain time? For example if i only wanted to count those employees who started in 2014? Or only those who started from June 2015 til present?

Thanks in advance, youve been a huge help so far

it would be inherently more complicated depending on the variables you wanted to include.

would you still want to include the manager and would there still be duplicate employee and start dates? the duplication drives a lot of complication.

trying to accommodate all the variables into 1 formula is a little difficult. the formula in H11 comes close for the month, year and month and year but doesn't include the manger.
the formula in I2 just takes the manager and the month and the year but all 3 would need to be specified.

hopefully its something you can work with

https://app.box.com/s/t9rasutaeti4gj13rh5ocb2b7sj5n2zi

And i thought these formula were complicated! I think I can use the formula in I2, luckily I am showing the results in a table and so i can specify the months/year/manager. I've edited it a little so I can drag the formula to apply to more cells without the reference points changing using $ and it seems to be working.

I will need to see if its possible to edit the formula so that it can auto-update as new entries are added

Thanks a load, you are a life saver