If Formula

[eramirez148]

How can I get the following results in column C and Column D

Column C = if column B hits 100% then the results in column C should say "yes" other wise empty cell
Column D = should create a percentage based on how many items hit 100% in column A. I have three products in column A product 1,2 and 3. If 2 out of three hit 100% then the return in column D should be 67%

Column A	Column B	Column C	Column D
Product 1	10%
Product 1	20%
Product 1	30%
Product 1	40%
Product 1	50%
Product 1	60%
Product 2	50%
Product 2	60%
Product 2	70%
Product 2	80%
Product 2	90%
Product 2	100%	Yes	67%
Product 2	110%
Product 2	120%
Product 3	50%
Product 3	60%
Product 3	70%
Product 3	100%	Yes	67%

 

[LegendaryAxe]

Does this work?

Book2
	A	B	C	D
1	Column A	Column B	Column C	Column D
2	Product 1	10%
3	Product 1	20%
4	Product 1	30%
5	Product 1	40%
6	Product 1	50%
7	Product 1	60%
8	Product 2	50%
9	Product 2	60%
10	Product 2	70%
11	Product 2	80%
12	Product 2	90%
13	Product 2	100%	Yes	67%
14	Product 2	110%
15	Product 2	120%
16	Product 3	50%
17	Product 3	60%
18	Product 3	70%
19	Product 3	100%	Yes	67%
Sheet5

Cell Formulas
Range	Formula
C2:C12	C2	=IF(B2=1,"Yes","")
D2:D19	D2	=IF(C2="Yes",COUNTIFS($B$2:$B$19,1)/COUNTA(UNIQUE($A$2:$A$19)),"")

 

[dreid1011]

What version of Excel are you using?

If you are using 365, here is a spill version:

=LET(a,A2:A19,b,B2:B19,HSTACK(BYROW(b,LAMBDA(r,IF(r=1,"Yes",""))),BYROW(b,LAMBDA(r,IF(r=1,COUNTIF(b,1)/COUNTA(UNIQUE(a)),"")))))