Trying to use countif formula, difficult to explain

[Andromeda1997]

Hi there,

I've been pulling my hair out on this one, I've got a large range about 1249 rows with 68 columns, due to privacy agreements I cant release any screenshots of the actual spreadsheet. So I have included an example below.

So I need to count the conformity within the 'lemons' category, the lemons category contains 2 questions and so I need to know if both those questions were considered a 'Conformity'.

The problem is I need it to search for the 'lemons' category and then only check that 'person'. It cannot be specific as the 'names' would change every month so Timmy annie and john would change to another name which I have no way of predicting, so it has to be a formula that will work without it.

I cant use macros due to the business policy.

Sorry for the weird explanation, happy to answer questions

 

[Michael M]

Can you upload this sample of data via XL2BB ( see my tag) please.
Posters don't really want to retype all of that information !

 

[Andromeda1997]

Can you upload this sample of data via XL2BB ( see my tag) please.
Posters don't really want to retype all of that information !

Unfortunately I cant download anything either, my computer is strictly managed by IT. Is the below okay? I don't have any formulas currently active.

	Lemons	Lemons	Oranges	Oranges	Oranges	Pears	Pears
	Did they meet target	were they sold in right region	Did they meet target	were they sold in right region	Were oranges sale price calculated correctly	Did they meet target	were they sold in right region		Lemons	Oranges	Pears
Timmy	Conformity	Non-Conformity	Conformity	Conformity	Conformity	Non-Conformity	Non-Conformity		50%​	100%​	0%​
Annie	Non-Conformity	Conformity	Non-Conformity	Non-Conformity	Non-Conformity	Non-Conformity	Conformity		50%​	0%​	50%​
John	Non-Conformity	Non-Conformity	Non-Conformity	Conformity	Non-Conformity	Conformity	Non-Conformity		0%​	33%​	50%​

 

[Michael M]

Bit rough, but try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Lemons	Lemons	Oranges	Oranges	Oranges	Pears	Pears
2		Did they meet target	were they sold in right region	Did they meet target	were they sold in right region	Were oranges sale price calculated correctly	Did they meet target	were they sold in right region		Lemons	Oranges	Pears
3	Timmy	Conformity	Non-Conformity	Conformity	Conformity	Conformity	Non-Conformity	Non-Conformity		50%	100%	0%
4	Annie	Non-Conformity	Conformity	Non-Conformity	Non-Conformity	Non-Conformity	Non-Conformity	Conformity		50%	0%	50%
5	John	Non-Conformity	Non-Conformity	Non-Conformity	Conformity	Non-Conformity	Conformity	Non-Conformity		0%	33%	50%
Sheet1

Cell Formulas
Range	Formula
J3:J5	J3	=COUNTIFS($B$1:$H$1,"Lemons",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Lemons")
K3:K5	K3	=COUNTIFS($B$1:$H$1,"Oranges",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Oranges")
L3:L5	L3	=COUNTIFS($B$1:$H$1,"Pears",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Pears")

 

[Andromeda1997]

Bit rough, but try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Lemons	Lemons	Oranges	Oranges	Oranges	Pears	Pears
2		Did they meet target	were they sold in right region	Did they meet target	were they sold in right region	Were oranges sale price calculated correctly	Did they meet target	were they sold in right region		Lemons	Oranges	Pears
3	Timmy	Conformity	Non-Conformity	Conformity	Conformity	Conformity	Non-Conformity	Non-Conformity		50%	100%	0%
4	Annie	Non-Conformity	Conformity	Non-Conformity	Non-Conformity	Non-Conformity	Non-Conformity	Conformity		50%	0%	50%
5	John	Non-Conformity	Non-Conformity	Non-Conformity	Conformity	Non-Conformity	Conformity	Non-Conformity		0%	33%	50%
Sheet1

Cell Formulas
Range	Formula
J3:J5	J3	=COUNTIFS($B$1:$H$1,"Lemons",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Lemons")
K3:K5	K3	=COUNTIFS($B$1:$H$1,"Oranges",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Oranges")
L3:L5	L3	=COUNTIFS($B$1:$H$1,"Pears",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Pears")

Yesssssssss

thank you!

I tried doing the countifs formula but always got an error, by this point I think I was overthinking it, thank you so much!!!!

 

[Michael M]

Glad to help...