How to calculate percentage based on conditions

Hi everyone,

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

Please see the attached sample excel

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.

I hope this make sense. Someone please help I am so damn close!

I don't understand how you are getting 60%? 42.86% seems correct to me. You have 7 visible, 3 of which are BAR and y/n = 1.

3/7 = 0.428571

Hi DannyJ!

Thank you!, but this is weird...when I enter the exact same formula into the TWH column in Table 2, it's given me the incorrect percentage. I'm getting 33.33% when it should be 50% b/c there is only one yes out of 2 responses for site TWH.

I changed the quotations to "TWH" instead of "BAR" and kept everything else the same. I'm not exactly sure what I'm doing wrong...

do you know? I don't even get why it's 33.33%? How is it getting that!

I'm having success with the following in M22, filled right:

=IFERROR(SUMPRODUCT(--($B$2:$B$12=M$21),($C$2:$C$12=1)*($D$2:$D$12))/(SUM($D$2:$D$12)-COUNTIFS($D$2:$D$12,1,$B$2:$B$12,"<>"&M$21)),0)

I have attached the spreadsheet with the formula as I sent them. I think you may be using the formula before I edited the post.

Originally Posted by DannyJ

Sorry, misunderstood. here you go.

=SUM(COUNTIFS($B$1:$B$12,"BAR", $C$1:$C$12,1,$D$1:$D$12,1)/COUNTIFS($B:$B,"BAR",$D:$D,1))
=SUM(COUNTIFS($B$1:$B$12,"TWH", $C$1:$C$12,1,$D$1:$D$12,1)/COUNTIFS($B:$B,"TWH",$D:$D,1))

Thank you! this works now!


sorry, one more question.

I'm trying to make these formulas work on a different sheet in excel. So all my data is on a sheet called "DATA"-which is where this formula is pulling everything from. However, I want to put this formula on a different sheet. How do I make this formula work by referencing the DATA sheet? I can't seem to put the sheet name DATA in the correct placement in the formula to make it work.

I'm so sorry and thank you for all your help!

No worries. Assuming everything is on the data tab, I believe it would be:

=SUM(COUNTIFS(Data!$B$1:$B$12,"BAR", Data!$C$1:$C$12,1,Data!$D$1:$D$12,1)/COUNTIFS(Data!$B:$B,"BAR",Data!$D:$D,1))