willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 893
- Office Version
- 365
- Platform
- Windows
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!
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 |