Trying to use countif formula, difficult to explain

Andromeda1997

New Member
Joined
Oct 5, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.

1601940664594.png


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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,409
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 5, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.


LemonsLemonsOrangesOrangesOrangesPearsPears
Did they meet targetwere they sold in right regionDid they meet targetwere they sold in right regionWere oranges sale price calculated correctlyDid they meet targetwere they sold in right regionLemonsOrangesPears
TimmyConformityNon-ConformityConformityConformityConformityNon-ConformityNon-Conformity
50%​
100%​
0%​
AnnieNon-ConformityConformityNon-ConformityNon-ConformityNon-ConformityNon-ConformityConformity
50%​
0%​
50%​
JohnNon-ConformityNon-ConformityNon-ConformityConformityNon-ConformityConformityNon-Conformity
0%​
33%​
50%​
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,409
Office Version
  1. 2013
Platform
  1. Windows
Bit rough, but try
Book1
ABCDEFGHIJKL
1LemonsLemonsOrangesOrangesOrangesPearsPears
2Did they meet targetwere they sold in right regionDid they meet targetwere they sold in right regionWere oranges sale price calculated correctlyDid they meet targetwere they sold in right regionLemonsOrangesPears
3TimmyConformityNon-ConformityConformityConformityConformityNon-ConformityNon-Conformity50%100%0%
4AnnieNon-ConformityConformityNon-ConformityNon-ConformityNon-ConformityNon-ConformityConformity50%0%50%
5JohnNon-ConformityNon-ConformityNon-ConformityConformityNon-ConformityConformityNon-Conformity0%33%50%
Sheet1
Cell Formulas
RangeFormula
J3:J5J3=COUNTIFS($B$1:$H$1,"Lemons",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Lemons")
K3:K5K3=COUNTIFS($B$1:$H$1,"Oranges",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Oranges")
L3:L5L3=COUNTIFS($B$1:$H$1,"Pears",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Pears")
 

Andromeda1997

New Member
Joined
Oct 5, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Bit rough, but try
Book1
ABCDEFGHIJKL
1LemonsLemonsOrangesOrangesOrangesPearsPears
2Did they meet targetwere they sold in right regionDid they meet targetwere they sold in right regionWere oranges sale price calculated correctlyDid they meet targetwere they sold in right regionLemonsOrangesPears
3TimmyConformityNon-ConformityConformityConformityConformityNon-ConformityNon-Conformity50%100%0%
4AnnieNon-ConformityConformityNon-ConformityNon-ConformityNon-ConformityNon-ConformityConformity50%0%50%
5JohnNon-ConformityNon-ConformityNon-ConformityConformityNon-ConformityConformityNon-Conformity0%33%50%
Sheet1
Cell Formulas
RangeFormula
J3:J5J3=COUNTIFS($B$1:$H$1,"Lemons",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Lemons")
K3:K5K3=COUNTIFS($B$1:$H$1,"Oranges",$B3:$H3,"Conformity")/COUNTIF($B$1:$H$1,"Oranges")
L3:L5L3=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!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,323
Messages
5,547,256
Members
410,782
Latest member
Colart
Top