COUNTA with conditions

[willow1985]

I am trying to write a complicated formula with countif and count A. Basically my goal is when M1 has the value "All" that all "Y" and "N" are counted.
However if M1 has the person's name it will only count the "Y" and "N" for that person. My end goal is to graph this so I can rather show everyone's "Y" and "N" result or by the individual person when you select specified values in M1.

Help would be greatly appreciated!

Safety Incentive.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										Y	N		William
2	Emp Name	PPE	Safe Lifting	Safety Knowledge	Safety Training	SWP	Unsafe Acts			1	1
3	William	Y	N	Y	Y	Y	N		Ashley	6	6
4	Steve	Y	Y	Y	Y	Y	N		Steve	6	6
5	Ashley	Y	Y	Y	N	Y	N		Test	6	6
6	Test	Y	Y	Y	N	Y	N		Test 2	6	6
7	Test 2	Y	N	Y	Y	Y	N		William	6	6
8
Sheet1

Cell Formulas
Range	Formula
J2:J7	J2	=IFERROR(IF(AND($M$1="All",$I$2="All"),COUNTIF(Table1[[PPE]:[Unsafe Acts]],$J$1),COUNTA(INDEX(Table1[[PPE]:[Unsafe Acts]],MATCH(I2,Table1[Emp Name],0),0))),"0")
K2:K7	K2	=IFERROR(IF(AND($M$1="All",$I$2="All"),COUNTIF(Table1[[PPE]:[Unsafe Acts]],$K$1),COUNTA(INDEX(Table1[[PPE]:[Unsafe Acts]],MATCH(I2,Table1[Emp Name],0),0))),"0")
I2	I2	=IF(M1="All","All","")
I3:I7	I3	=IF(M1="All","",UNIQUE(SORT(Table1[Emp Name])))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B3:G7	List	Y,N

 

[horrellbt01]

Is there a reason you do not want to use a pivot chart for this?

 

[willow1985]

Is there a reason you do not want to use a pivot chart for this?

To be honest I am not sure how to get the desired effect with a pivot table either as I do not want to include the column headers: PPE, Safe Lifting etc. I only want to count Y or N for all or by employee

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										Y	N		william
2	Emp Name	PPE	Safe Lifting	Safety Knowledge	Safety Training	SWP	Unsafe Acts
3	William	Y	N	Y	Y	Y	N		Ashley	0	0
4	Steve	Y	Y	Y	Y	Y	N		Steve	0	0
5	Ashley	Y	Y	Y	N	Y	N		Test	0	0
6	Test	Y	Y	Y	N	Y	N		Test 2	0	0
7	Test 2	Y	N	Y	Y	Y	N		William	4	2
8
Main

Cell Formulas
Range	Formula
I2	I2	=IF(M1="All","All","")
J2	J2	=IF(M1="all",COUNTIFS(Table1[[PPE]:[Unsafe Acts]],J1:K1),"")
I3:I7	I3	=IF(M1="All","",UNIQUE(SORT(Table1[Emp Name])))
J3:J7	J3	=IF(M1="all","",MMULT((Table1[[PPE]:[Unsafe Acts]]=J1)*(I3#=M1),SEQUENCE(COLUMNS(Table1[[#Headers],[PPE]:[Unsafe Acts]]),,,0)))
K3:K7	K3	=IF(M1="all","",MMULT((Table1[[PPE]:[Unsafe Acts]]=K1)*(I3#=M1),SEQUENCE(COLUMNS(Table1[[#Headers],[PPE]:[Unsafe Acts]]),,,0)))
Dynamic array formulas.

 

[horrellbt01]

To be honest I am not sure how to get the desired effect with a pivot table either as I do not want to include the column headers: PPE, Safe Lifting etc. I only want to count Y or N for all or by employee

Add a column to the right of your data titled Y
=countif(B3:G3,"Y")

Add a second collumn to the right of that called N
=countif(B3:G3,"N")

These will give you the total number for each employee, which is what you can drop into the values section of the pivot table. Then you can filter by Employee Name or by all

 

[willow1985]

I entered it wrong at first but now seems like it is working perfectly

 

[willow1985]

Add a column to the right of your data titled Y
=countif(B3:G3,"Y")

Add a second collumn to the right of that called N
=countif(B3:G3,"N")

These will give you the total number for each employee, which is what you can drop into the values section of the pivot table. Then you can filter by Employee Name or by all

Thank you very much!!

 

[willow1985]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										Y	N		william
2	Emp Name	PPE	Safe Lifting	Safety Knowledge	Safety Training	SWP	Unsafe Acts
3	William	Y	N	Y	Y	Y	N		Ashley	0	0
4	Steve	Y	Y	Y	Y	Y	N		Steve	0	0
5	Ashley	Y	Y	Y	N	Y	N		Test	0	0
6	Test	Y	Y	Y	N	Y	N		Test 2	0	0
7	Test 2	Y	N	Y	Y	Y	N		William	4	2
8
Main

Cell Formulas
Range	Formula
I2	I2	=IF(M1="All","All","")
J2	J2	=IF(M1="all",COUNTIFS(Table1[[PPE]:[Unsafe Acts]],J1:K1),"")
I3:I7	I3	=IF(M1="All","",UNIQUE(SORT(Table1[Emp Name])))
J3:J7	J3	=IF(M1="all","",MMULT((Table1[[PPE]:[Unsafe Acts]]=J1)*(I3#=M1),SEQUENCE(COLUMNS(Table1[[#Headers],[PPE]:[Unsafe Acts]]),,,0)))
K3:K7	K3	=IF(M1="all","",MMULT((Table1[[PPE]:[Unsafe Acts]]=K1)*(I3#=M1),SEQUENCE(COLUMNS(Table1[[#Headers],[PPE]:[Unsafe Acts]]),,,0)))
Dynamic array formulas.

Thank you very much Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[steve the fish]

Not sure id ever want a chart to look like that. Are you sure thats best solution?