Count ONCE if a unique value exists across one of multiple columns + meets other criteria

[robmsmith]

Hi all.

This is my first time posting so bear with me with the explanations. I have included a sample table with data below to illustrate a challenge I have. I want to generate headcount and employee turnover data by management chain. i.e. Everyone who reports up the chain to 'x' executive.

To do this i have an extract from a system like below (I cannot change this unfortunately). There are thousands of rows of data in reality - covering both active and terminated employees.

I have already successfully calculated the total headcount as of the beginning of Q42020.

=COUNTIFS(C2:5000,"<1/1/2021",D2:D5000,""+COUNTIFS(C2:C5000,"<1/10/2021",'D2:D5000,">=1/1/2021")

I now want to use the same or similar logic as above but to count the headcount of all people within an Executive's team. e.g. to count all active employees who ultimately report up to Joe Bloggs at the end of Q4 2020 . There are multiple columns for management chain (I just included three for example here). So I want to be able to make a formula to answer the following:

COUNT WORKER IF JOE BLOGGS APPEARS IN COLUMN E, F OR G, AND THE HIRE DATE IS BEFORE 1/1/2021, AND TERMINATION DATE IS BLANK OR MORE THAN OR EQUAL TO 1/1/2021

Obviously i cannot use COUNTIFS to count if Joe Bloggs appears in column E, F, OR G and I have failed so far to make a SUM PRODUCT or other alternative to achieve this...Can anyone help? It's really bugging me! Many thanks.

Worker (COLUMN A)	Employee ID (B)	Hire Date (C)	Termination Date (D)	Management Chain - Level 01 (E)	Management Chain - Level 02 (F)	Management Chain - Level 03 (G)
James Young	1911456967	01/10/2019​		Joe Bloggs	Joe Bloggs	John Smith
Bill Brian	1099809316	01/08/2019​		Andy White	Peter North	Phil Gorman
Tom Peak	2004458391	08/01/2020​	20/01/2020​	Tim Booth	Tim Booth	Joe Bloggs
Caroline Fell	2006461052	29/06/2020​	31/07/2020​	Tom Fullerson	Brian East	Peter North
Jade Smith	1202358187	01/08/2019​		Sarah Smith	Sarah Smith	Martin Jones
Rebecca Marlow	2010457287	21/10/2020​		Rebecca South	Tim Booth	Sarah Smith

 

[Toadstool]

Hi RobMSmith,

I'm a little confused as Tom Peak was terminated Jan 2020 but the described logic doesn't seem to exclude from the headcount?
Should it be Termination Date = blank OR Termination Date > 31 December 2020?

P.S. Can I use a worker column?

 

[robmsmith]

Hi RobMSmith,

I'm a little confused as Tom Peak was terminated Jan 2020 but the described logic doesn't seem to exclude from the headcount?
Should it be Termination Date = blank OR Termination Date > 31 December 2020?

P.S. Can I use a worker column?

Hi there.

Thanks for the clarification.

The worker terminated in Jan 2020 would be excluded from the logic as the formula only picks up those hired before 2021 and with a blank cell in the termination date column (i.e. Meaning they are still active).

I'm not clear on your worker column question but I'm open to any and all suggestions so far. Ideally I don't want to be adding columns to the data source but if there is no other way then of course I will make do.

Thanks again. Rob

 

[Toadstool]

RobMSmith.xlsx
	B	C	D	E	F	G	H	I
1	Employee ID (B)	Hire Date (C)	Termination Date (D)	Management Chain - Level 01 (E)	Management Chain - Level 02 (F)	Management Chain - Level 03 (G)	Joe Bloggs	1
2	1911456967	01-Oct-19		Joe Bloggs	Joe Bloggs	John Smith	1
3	1099809316	01-Aug-19		Andy White	Peter North	Phil Gorman	0
4	2004458391	08-Jan-20	20-Jan-20	Tim Booth	Tim Booth	Joe Bloggs	0
5	2006461052	29-Jun-20	31-Jul-20	Tom Fullerson	Brian East	Peter North	0
6	1202358187	01-Aug-19		Sarah Smith	Sarah Smith	Martin Jones	0
7	2010457287	21-Oct-20		Rebecca South	Tim Booth	Sarah Smith	0
Sheet1

Cell Formulas
Range	Formula
I1	I1	=SUM(H2:H5000)
H2:H7	H2	=IF(AND(C2<DATE(2021,1,1),OR(D2>DATE(2021,1,1),D2=""),NOT(ISNA(MATCH($H$1,$E2:$G2,0)))),1,0)

 

[robmsmith]

Hi Toadstool. All.

Thanks a lot for this, much appreciated - this certainly gets to the answer via the raw data.

I wonder if on reflection there is a way to get this calc to be done in one cell? Reason being is i will have the headcount for this manager but then subsequently plan to calculate a bunch of other metrics for them - such as the termination rate, number of new starters etc. So a repeatable one cell formula to identify the managers and then be able to add the extra criteria is the best case.

For example I am looking to have the raw data on one sheet and then a separate tab with a table like below with the formulas generating the data in each cell for each senior manager:

Q4 2020

Manager Name	Headcount	Leavers	Starters	etc.
Joe Bloggs	FORMULA LIKE ABOVE WOULD BE HERE to give that singular number	Formula with different criteria here - but still identifying workers reporting to Joe Bloggs in Management chain	etc.
Peter North
Tom Fullerson etc.

 

[Toadstool]

I think you may need a VBA solution.
Personally I'd revisit the data design which duplicates a Manager at different levels.

 

[robmsmith]

Thanks figured as much. Well your help is much appreciated all the same.

It made me consider there's a slightly different way around it (to your point of maybe attacking this by using a helper column in the source data). Is there a formula to search multiple name options and return the one that appears in a cell.

I found a way to extract all names in the management chain for each worker into one single cell.

I wonder if there's a way to to then make a formula to text search for the execs and return the value of the one that appears in the cell.

i.e. Search for Joe Bloggs, Andy White, Tim Booth, Sarah Smith, Rebecca South in column A and return the matching text string to Column B. Then it would give me a single column with the name of each exec in it against the worker, and i could complete the countifs statement from that.

COLUMN A All Managers in Management Chain	COLUMN B Return the name if it appears in
Joe Bloggs Toni Simmons Henry Mcgregor Rachel Gascoigne Emma Hulbert Chris Stafford

 

[robmsmith]

To clarify the cell in Column A comes out with the names in one single cell and line spaced apart, so some kind of text search or string lookup may be needed. I am looking at those options now.

 

[robmsmith]

To clarify the cell in Column A comes out with the names in one single cell and line spaced apart, so some kind of text search or string lookup may be needed. I am looking at those options now.

Actually think i may have found it - =IF(ISNUMBER(SEARCH("Joe Bloggs",A2)),"Joe Bloggs",IF(ISNUMBER(SEARCH("Andy White",A2)),"Andy White",""))

Then can drag down.... Seems to work!