Formula Help: Calculating a percentage

[chiccaboom]

Hi everyone,

I am so close to solving this formula but I can't figure it out. Please help!

year	site	y/n	helper cell
2017	TWH	0	1
2017	TWH	1	1
2017	BAR	1	1
2017	BAR	1	1
2017	BAR	1	1
2017	BAR	0	1
2017	BAR	0	1

<tbody>
</tbody>

What I want to happen is that when you filter "YEAR" for either 2016 or 2017, I want Table 2. to automatically calculate the percentage of YES=1 from column "Y/N" but ONLY OUT OF the total responses for the specified site indicated in Table 2.

So for example, if you filter YEAR for 2017, in Table 2. the percentage for site TWH should show up as 50% b/c there is only one 1 out of 2 responses for TWH.

Similarly, for site BAR, in Table 2 the percentage should be 60%.

However, what is happening, is that the formula in Table 2. is calculating the number of Yes=1 for each site out of ALL the responses in the Y/N column.

 

[63falcondude]

You might be able to use the subtotal function.

This will show the % of 1's in column C:

=SUBTOTAL(9,C:C)/SUBTOTAL(2,C:C)

 

[AlanY]

try

Excel 2012
	A	B	C
1	year	site	y/n
2	2017	TWH	0
3	2017	TWH	1
4	2017	BAR	1
5	2017	BAR	1
6	2017	BAR	1
7	2017	BAR	0
8	2017	BAR	0
9
10
11	year	site
12	2017	TWH	50%
13	2017	BAR	60%
Sheet3

Cell Formulas
Range	Formula
C12	=COUNTIFS($B$2:$B$8,B12,$C$2:$C$8,1)/COUNTIFS($A$2:$A$8,A12,$B$2:$B$8,B12)

 

[Scott R]

=subtotal(109, c:c) / subtotal(102, c:c)