Using Countif with autofillter
 

I am trying to use auto filter with the "countif" function?


This Formula gives me a percent for the hole document:

=(COUNTIF(AK3:AK5000,9)+COUNTIF(AK3:AK5000,10))/COUNT(AK3:AK5000)

I want to be able to auto filter the data and have the percent from the
above function change to reflect only the visible info. Basically trying to
count only the number 9 and the number 10 for the visible info divided by the
total number of results within the same column.

ANYHELP????

Using Countif with autofillter
 

Here you go


=SUMPRODUCT(($AK$3:$AK$5000={9,10})*(SUBTOTAL(3,OF FSET($AK$3,ROW($AK$3:$AK$5000)-MIN(ROW($AK$3:$AK$5000)),,))))/SUBTOTAL(2,$A$3:$AK$5000)



--


Regards,


Peo Sjoblom



"John" wrote in message
...
I am trying to use auto filter with the "countif" function?


This Formula gives me a percent for the hole document:

=(COUNTIF(AK3:AK5000,9)+COUNTIF(AK3:AK5000,10))/COUNT(AK3:AK5000)

I want to be able to auto filter the data and have the percent from the
above function change to reflect only the visible info. Basically trying
to
count only the number 9 and the number 10 for the visible info divided by
the
total number of results within the same column.

ANYHELP????

Using Countif with autofillter
 

Using this formula worked with the exception that the end result for example
was 8% yet the answer should have been 80% how to fix that?

"Peo Sjoblom" wrote:

Here you go


=SUMPRODUCT(($AK$3:$AK$5000={9,10})*(SUBTOTAL(3,OF FSET($AK$3,ROW($AK$3:$AK$5000)-MIN(ROW($AK$3:$AK$5000)),,))))/SUBTOTAL(2,$A$3:$AK$5000)



--


Regards,


Peo Sjoblom



"John" wrote in message
...
I am trying to use auto filter with the "countif" function?


This Formula gives me a percent for the hole document:

=(COUNTIF(AK3:AK5000,9)+COUNTIF(AK3:AK5000,10))/COUNT(AK3:AK5000)

I want to be able to auto filter the data and have the percent from the
above function change to reflect only the visible info. Basically trying
to
count only the number 9 and the number 10 for the visible info divided by
the
total number of results within the same column.

ANYHELP????

Using Countif with autofillter
 

I had a typo in the count formula ( I used A3 instead of AK3)
apologies

try


=SUMPRODUCT(($AK$3:$AK$5000={9,10})*(SUBTOTAL(3,OF FSET($AK$3,ROW($AK$3:$AK$5000)-MIN(ROW($AK$3:$AK$5000)),,))))/SUBTOTAL(2,$AK$3:$AK$5000)




--


Regards,


Peo Sjoblom







"John" wrote in message
...
Using this formula worked with the exception that the end result for
example
was 8% yet the answer should have been 80% how to fix that?

"Peo Sjoblom" wrote:

Here you go


=SUMPRODUCT(($AK$3:$AK$5000={9,10})*(SUBTOTAL(3,OF FSET($AK$3,ROW($AK$3:$AK$5000)-MIN(ROW($AK$3:$AK$5000)),,))))/SUBTOTAL(2,$A$3:$AK$5000)



--


Regards,


Peo Sjoblom



"John" wrote in message
...
I am trying to use auto filter with the "countif" function?


This Formula gives me a percent for the hole document:

=(COUNTIF(AK3:AK5000,9)+COUNTIF(AK3:AK5000,10))/COUNT(AK3:AK5000)

I want to be able to auto filter the data and have the percent from the
above function change to reflect only the visible info. Basically
trying
to
count only the number 9 and the number 10 for the visible info divided
by
the
total number of results within the same column.

ANYHELP????

Using Countif with autofillter
 

NICE

Thanks a bunch. I was lost.

"Peo Sjoblom" wrote:

I had a typo in the count formula ( I used A3 instead of AK3)
apologies

try


=SUMPRODUCT(($AK$3:$AK$5000={9,10})*(SUBTOTAL(3,OF FSET($AK$3,ROW($AK$3:$AK$5000)-MIN(ROW($AK$3:$AK$5000)),,))))/SUBTOTAL(2,$AK$3:$AK$5000)




--


Regards,


Peo Sjoblom







"John" wrote in message
...
Using this formula worked with the exception that the end result for
example
was 8% yet the answer should have been 80% how to fix that?

"Peo Sjoblom" wrote:

Here you go


=SUMPRODUCT(($AK$3:$AK$5000={9,10})*(SUBTOTAL(3,OF FSET($AK$3,ROW($AK$3:$AK$5000)-MIN(ROW($AK$3:$AK$5000)),,))))/SUBTOTAL(2,$A$3:$AK$5000)



--


Regards,


Peo Sjoblom



"John" wrote in message
...
I am trying to use auto filter with the "countif" function?


This Formula gives me a percent for the hole document:

=(COUNTIF(AK3:AK5000,9)+COUNTIF(AK3:AK5000,10))/COUNT(AK3:AK5000)

I want to be able to auto filter the data and have the percent from the
above function change to reflect only the visible info. Basically
trying
to
count only the number 9 and the number 10 for the visible info divided
by
the
total number of results within the same column.

ANYHELP????