SOLVED

Countif in countifs

%3CLINGO-SUB%20id%3D%22lingo-sub-3291438%22%20slang%3D%22en-US%22%3ECountif%20in%20countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291438%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20question%20about%20using%20a%20countif%20within%20a%20countifs-formula.%3C%2FP%3E%3CP%3EI%20want%20to%20guarantee%20some%20kind%20of%20anonymity%2C%20therefore%20I%20only%20want%20to%20calculate%20the%20percentage%20if%20at%20least%2010%20persons%20are%20part%20of%20a%20specific%20category.%20The%20red%20part%20in%20the%20formula%20below%20is%20not%20working%2C%20so%20I%20am%20wondering%20if%20something%20else%20might%20fix%20this%20problem%20for%20me!%20Because%20I%20have%20to%20do%20this%20for%20a%20lot%20of%20percentages%20I%20want%20to%20fix%20it%20in%20the%20formula!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(COUNTIFS(K%3AK%3B1%3BN%3AN%3B1%3CFONT%20color%3D%22%23DF0000%22%3E%3BCOUNTIF(N%3AN%3B1)%3B%22%26gt%3B9%22%3C%2FFONT%3E)%2FCOUNTIFS(K%3AK%3B%22%26gt%3B0%22%3BK%3AK%3B%22%26lt%3B6%22%3BN%3AN%3B1))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20thinking%20along!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3291438%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3292006%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20in%20countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3292006%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20quick%20solution%2C%20this%20works%20great!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3291468%22%20slang%3D%22en-US%22%3ERe%3A%20Countif%20in%20countifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3291468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1369646%22%20target%3D%22_blank%22%3E%40Klaasw9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E%3DIF(COUNTIF(N%3AN%3B1)%26gt%3B9%3BCOUNTIFS(K%3AK%3B1%3BN%3AN%3B1)%2FCOUNTIFS(K%3AK%3B%22%26gt%3B0%22%3BK%3AK%3B%22%26lt%3B6%22%3BN%3AN%3B1)%3B%22%22)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

I have a question about using a countif within a countifs-formula.

I want to guarantee some kind of anonymity, therefore I only want to calculate the percentage if at least 10 persons are part of a specific category. The red part in the formula below is not working, so I am wondering if something else might fix this problem for me! Because I have to do this for a lot of percentages I want to fix it in the formula!

 

=(COUNTIFS(K:K;1;N:N;1;COUNTIF(N:N;1);">9")/COUNTIFS(K:K;">0";K:K;"<6";N:N;1))

 

Thank you in advance for thinking along!

2 Replies
best response confirmed by Klaasw9 (New Contributor)
Solution

@Klaasw9 

Perhaps

 

=IF(COUNTIF(N:N;1)>9;COUNTIFS(K:K;1;N:N;1)/COUNTIFS(K:K;">0";K:K;"<6";N:N;1);"")

Thank you for the quick solution, this works great!