Return Value With Multiple Criteria

[kumara_faith]

Hi ,

My current input sheet looks as below:

Book1
	B	C	D	E	F	G
3	Branch	Name	Employment Status	Primary Branch	HR Position	Job Grade
4	New York	Kelly	Permanent	New York	Branch Head	3B
5	New York	Michelle	Contract		Executive	1A
6	New York	Michael	Permanent		Executive	1A
7	Beoumont	John	Permanent	Beoumont	Branch Head	3B
8	Beoumont	Jenny	Permanent		Executive	2B
9	Beoumont	Josh	Permanent		Executive	2B
10	Beoumont	Maria	Permanent		Executive	2B
11	Chicago	John	Permanent		Branch Head	3B
12	Chicago	Valdez	Contract		Executive	1B
13	Chicago	Juarez	Contract		Executive	1A
14	Nashville	Manuel	Contract		Executive	2B
15	Nashville	Thomas	Contract		Executive	2A
16	Nashville	Kelly	Permanent		Branch Head	3B
Sheet1

I need to summarize the headcount in two tables. The first table is as below:

Book1
	I	J	K	L	M	N	O	P
3	Branch	Total Headcount	1A	1B	2A	2B	3A	3B
4	New York	3
5	Beoumont	4
6	Chicago	2
7	Nashville	2
Sheet1

Cell Formulas
Range	Formula
J4:J7	J4	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>I4)),COUNT(FILTER(ROW($C$4:$C$16),($B$4:$B$16=I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))

In the table above, I have managed to utilize the solution provided earlier for column J. The headcount is Nashville is only two because Kelly's primary branch is New York and she will be accounted for in New York and not Nashville. The same will apply for John.

The rest of the columns are job grades for all employees. The job grades should also remove any duplicates. For example, Kelly is job grade 3B and John is also 3B. So job grade 3B should only show 2 which is one for New York and one for Beoumont. I tried modifying the existing formula as below but did not work:

=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>I4)),COUNT(FILTER(ROW($C$4:$C$16),($B$4:$B$16=I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))*COUNTIFS(B4:B16,I4,G4:G16,K3)

Is there a way to modify the formula to show the desired value for the job grades?


In the second table, I need to summarize as follows:

Book1
	I	J	K	L
10	Branch	Branch Head	Permanent	Contract
11	New York	1
12	Beoumont	1
13	Chicago	1
14	Nashville	1
Sheet1

Cell Formulas
Range	Formula
J11:J14	J11	=COUNTIFS($B$4:$B$16,I11,$F$4:$F$16,J$10)

The current formula I am using shows incorrect value. For example, I only have two branch heads which is Kelly and John. Therefore the correct value should be one in New York and one in Beoumont. Example , Kelly takes care of both New York and Nashville but her primary branch is New York and therefore should be allocated only in New York. The same should apply for the employment status of contract and permanent. We have only 6 permanent staffs and not 8 as Kelly and John is counted as each one and not double.

Is there a way to modify the existing formula to show the correct value for Branch Head, Permanent and Contract ?

Appreciate all the help.?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3		Branch	Name	Employment Status	Primary Branch	HR Position	Job Grade		Branch	Total Headcount	1A	1B	2A	2B	3A	3B
4		New York	Kelly	Permanent	New York	Branch Head	3B		New York	3	2	0	0	0	0	1
5		New York	Michelle	Contract		Executive	1A		Beoumont	4	0	0	0	3	0	1
6		New York	Michael	Permanent		Executive	1A		Chicago	2	1	1	0	0	0	0
7		Beoumont	John	Permanent	Beoumont	Branch Head	3B		Nashville	2	0	0	1	1	0	0
8		Beoumont	Jenny	Permanent		Executive	2B
9		Beoumont	Josh	Permanent		Executive	2B
10		Beoumont	Maria	Permanent		Executive	2B		Branch	Branch Head	Permanent	Contract
11		Chicago	John	Permanent		Branch Head	3B		New York	1	2	1
12		Chicago	Valdez	Contract		Executive	1B		Beoumont	1	4	0
13		Chicago	Juarez	Contract		Executive	1A		Chicago	0	0	2
14		Nashville	Manuel	Contract		Executive	2B		Nashville	0	0	2
15		Nashville	Thomas	Contract		Executive	2A
16		Nashville	Kelly	Permanent		Branch Head	3B
17
Input

Cell Formulas
Range	Formula
J4:J7	J4	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>I4)),COUNT(FILTER(ROW($C$4:$C$16),($B$4:$B$16=I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))
K4:P7	K4	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>$I4)),COUNT(FILTER(ROW($C$4:$C$16),($G$4:$G$16=K$3)*($B$4:$B$16=$I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))
J11:J14	J11	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>$I4)),COUNT(FILTER(ROW($C$4:$C$16),($F$4:$F$16=J$10)*($B$4:$B$16=$I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))
K11:L14	K11	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>$I4)),COUNT(FILTER(ROW($C$4:$C$16),($D$4:$D$16=K$10)*($B$4:$B$16=$I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))

 

[Marcelo Branco]

Another

Pasta1
	I	J	K	L	M	N	O	P
1	Branch	Total Headcount	1A	1B	2A	2B	3A	3B
2	New York	3	2	0	0	0	0	1
3	Beoumont	4	0	0	0	3	0	1
4	Chicago	2	1	1	0	0	0	0
5	Nashville	2	0	0	1	1	0	0
Plan3

Cell Formulas
Range	Formula
J2:J5	J2	=COUNTIF($B:$B,$I2)-SUMPRODUCT(--($B$2:$B$100=$I2),COUNTIFS($C$2:$C$100,$C$2:$C$100,$E$2:$E$100,"<>"&$I2,$E$2:$E$100,"<>"))
K2:P5	K2	=COUNTIFS($B:$B,$I2,$G:$G,K$1)-SUMPRODUCT(--($B$2:$B$100=$I2),--($G$2:$G$100=K$1),COUNTIFS($C$2:$C$100,$C$2:$C$100,$E$2:$E$100,"<>"&$I2,$E$2:$E$100,"<>"))

M.

 

[Marcelo Branco]

oops

Correction

Pasta1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Branch	Name	Employment Status	Primary Branch	HR Position	Job Grade		Branch	Total Headcount	1A	1B	2A	2B	3A	3B
4	New York	Kelly	Permanent	New York	Branch Head	3B		New York	3	2	0	0	0	0	1
5	New York	Michelle	Contract		Executive	1A		Beoumont	4	0	0	0	3	0	1
6	New York	Michael	Permanent		Executive	1A		Chicago	2	1	1	0	0	0	0
7	Beoumont	John	Permanent	Beoumont	Branch Head	3B		Nashville	2	0	0	1	1	0	0
8	Beoumont	Jenny	Permanent		Executive	2B
9	Beoumont	Josh	Permanent		Executive	2B
10	Beoumont	Maria	Permanent		Executive	2B
11	Chicago	John	Permanent		Branch Head	3B
12	Chicago	Valdez	Contract		Executive	1B		Branch	Branch Head	Permanent	Contract
13	Chicago	Juarez	Contract		Executive	1A		New York	1	2	1
14	Nashville	Manuel	Contract		Executive	2B		Beoumont	1	4	0
15	Nashville	Thomas	Contract		Executive	2A		Chicago	0	0	2
16	Nashville	Kelly	Permanent		Branch Head	3B		Nashville	0	0	2
17
Plan3

Cell Formulas
Range	Formula
J4:J7	J4	=COUNTIF($B:$B,$I4)-SUMPRODUCT(--($B$4:$B$100=$I4),COUNTIFS($C$4:$C$100,$C$4:$C$100,$E$4:$E$100,"<>"&$I4,$E$4:$E$100,"<>"))
K4:P7	K4	=COUNTIFS($B:$B,$I4,$G:$G,K$3)-SUMPRODUCT(--($B$4:$B$100=$I4),--($G$4:$G$100=K$3),COUNTIFS($C$4:$C$100,$C$4:$C$100,$E$4:$E$100,"<>"&$I4,$E$4:$E$100,"<>"))
J13:J16	J13	=COUNTIFS($B$4:$B$100,$I13,$F$4:$F$100,J$12)-SUMPRODUCT(--($B$4:$B$100=$I13),--($F$4:$F$100=J$12),COUNTIFS($C$4:$C$100,$C$4:$C$100,$E$4:$E$100,"<>"&$I13,$E$4:$E$100,"<>"))
K13:L16	K13	=COUNTIFS($B$4:$B$100,$I13,$D$4:$D$100,K$12)-SUMPRODUCT(--($B$4:$B$100=$I13),--($D$4:$D$100=K$12),COUNTIFS($C$4:$C$100,$C$4:$C$100,$E$4:$E$100,"<>"&$I13,$E$4:$E$100,"<>"))

M.

 

[kumara_faith]

Hi Fluff and Marcelo,

Thank you so much for your valuable time and patience. Both solution worked and have a great day ahead.?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[kumara_faith]

Hi,

My apologies. I have another criteria as follows:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Branch	Name	Employment Status	Primary Branch	HR Position	Job Grade		Branch	Total Headcount	1A	1B	2A	2B	3A
4	New York	Kelly	Permanent	New York	Branch Head	3B		New York	3	2	0	0	0	0
5	New York	Michelle	Contract		Executive	1A		Beoumont	4	0	0	0	3	0
6	New York	Michael	Permanent		Executive	1A		Chicago	2	1	1	0	0	0
7	Beoumont	John	Permanent	Beoumont	Branch Head	3B		Nashville	2	0	0	1	1	0
8	Beoumont	Jenny	Permanent		Executive	2B
9	Beoumont	Josh	Permanent		Executive	2B
10	Beoumont	Maria	Permanent		Executive	2B		Branch	Branch Head	Permanent	Contract
11	Chicago	John	Permanent		Branch Head	3B		New York	1	2	1
12	Chicago	Valdez	Contract		Executive	1B		Beoumont	1	4	0
13	Chicago	Juarez	Contract		Executive	1A		Chicago	0	0	2
14	Nashville	Manuel	Contract		Executive	2B		Nashville	0	0	1
15	Nashville	Thomas	Retired		Executive	2A
16	Nashville	Kelly	Permanent		Branch Head	3B
Sheet1

Cell Formulas
Range	Formula
J4:J7	J4	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>I4)),COUNT(FILTER(ROW($C$4:$C$16),($B$4:$B$16=I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))
K4:O7	K4	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>$I4)),COUNT(FILTER(ROW($C$4:$C$16),($G$4:$G$16=K$3)*($B$4:$B$16=$I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))
J11:J14	J11	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>$I4)),COUNT(FILTER(ROW($C$4:$C$16),($F$4:$F$16=J$10)*($B$4:$B$16=$I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))
K11:L14	K11	=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>$I4)),COUNT(FILTER(ROW($C$4:$C$16),($D$4:$D$16=K$10)*($B$4:$B$16=$I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))

I also have the employment status as "Retired". This headcount should not be counted . In cell M7, this headcount is counted in the current solution. Is there a way to modify the formula to exclude retired headcounts ? Appreciate all the help.

 

[Fluff]

How about

=LET(p,FILTER($C$4:$C$16,($E$4:$E$16<>"")*($E$4:$E$16<>$I4)),COUNT(FILTER(ROW($C$4:$C$16),($D$4:$D$16<>"retired")*($G$4:$G$16=K$3)*($B$4:$B$16=$I4)*(ISNA(MATCH($C$4:$C$16,p,0))))))

 

[kumara_faith]

Hi Fluff,

That worked. Thank you for your patience and valuable time. Have a great day.

 

[Fluff]

You're welcome & thanks for the feedback.