COUNTIF Issues - Counting across columns

[DSL1970]

All,

I have struggling with a formula to use IF and AND. I have 3 columns in a table, Chain of Command 1, Chain of Command 2 and a Full Time/Part Time column. This list will be added to each week, so it will continue to grow.

Based on a preselected Leader I have been able to get a count of on many times a leader shows up in the list, but I haven't been able to determine how I only use this leader and determine how many are FT employees. This number can only be equal to or less than the count - which makes sense. So listed below I have the correct count formula, but struggling on getting the count of FT based on the leader (including my 40th attempt to get this to work!!!). Any help is appreciated

[TABLE="width: 812"]
<tbody>[TR]
[TD]Which Leader?[/TD]
[TD]Count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 1[/TD]
[TD]=COUNTIF(A5:B24,A2)[/TD]
[TD="colspan: 4"]=COUNTIF(AND((A5:B24,A2), C5:C24="FT"),COUNTIF( C5:C24="FT"),"")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CHAIN OF COMMAND 1[/TD]
[TD]CHAIN OF COMMAND 2[/TD]
[TD]PT/FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 1[/TD]
[TD]LEADER 19[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 1[/TD]
[TD]LEADER 20[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 1[/TD]
[TD]LEADER 21[/TD]
[TD]PT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 2[/TD]
[TD]LEADER 22[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 3[/TD]
[TD]LEADER 23[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 4[/TD]
[TD]LEADER 24[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 5[/TD]
[TD]LEADER 25[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 6[/TD]
[TD]LEADER 26[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 7[/TD]
[TD]LEADER 27[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 8[/TD]
[TD]LEADER 28[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 9[/TD]
[TD]LEADER 29[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 10[/TD]
[TD]LEADER 30[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 11[/TD]
[TD]LEADER 31[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 12[/TD]
[TD]LEADER 32[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 13[/TD]
[TD]LEADER 33[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 14[/TD]
[TD]LEADER 34[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 15[/TD]
[TD]LEADER 35[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 16[/TD]
[TD]LEADER 36[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 17[/TD]
[TD]LEADER 37[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LEADER 18[/TD]
[TD]LEADER 38[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

COUNTIFS

Book1
	A	B	C
1	LEADER 1	FT	2
2
3	CHAIN OF COMMAND 1	CHAIN OF COMMAND 2	PT/FT
4	LEADER 1	LEADER 19	FT
5	LEADER 1	LEADER 20	FT
6	LEADER 1	LEADER 21	PT
7	LEADER 2	LEADER 22	FT
8	LEADER 3	LEADER 23	FT
9	LEADER 4	LEADER 24	FT
10	LEADER 5	LEADER 25	FT
11	LEADER 6	LEADER 26	FT
12	LEADER 7	LEADER 27	FT
13	LEADER 8	LEADER 28	FT
14	LEADER 9	LEADER 29	FT
15	LEADER 10	LEADER 30	FT
16	LEADER 11	LEADER 31	FT
17	LEADER 12	LEADER 32	FT
18	LEADER 13	LEADER 33	FT
19	LEADER 14	LEADER 34	FT
Sheet3

Cell Formulas
Range	Formula
C1	=COUNTIFS($A$4:$A$23,$A$1,$C$4:$C$23,$B$1)

 

[jtakw]

Hi,

Also posted here: https://www.mrexcel.com/forum/excel...ng-across-columns.html?highlight=#post5040170