Hi,

I'm trying to get a simple conditional formula to work when I Auto Filter.

How can this be done?

I need to calculate the percentage of visible cells that have 1 in them of
the total visible cells

=COUNTIF(G9:G99,1)/COUNTA(G9:G99)

Filtered range is A2:B15

Filtered on column A with column B containing some 1's:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B15,ROW(B2:B15)-MIN(ROW(B2:B15)),,1)),--(B2:B15=1))/SUBTOTAL(3,A2:A15)

Format as PERCENTAGE

--
Biff
Microsoft Excel MVP


"tonyv" <(E-Mail Removed)> wrote in message
news:1BC58AE5-B6DC-4F2E-B011-(E-Mail Removed)...
> Hi,
>
> I'm trying to get a simple conditional formula to work when I Auto Filter.
>
> How can this be done?
>
> I need to calculate the percentage of visible cells that have 1 in them of
> the total visible cells
>
> =COUNTIF(G9:G99,1)/COUNTA(G9:G99)

Assuming your data is in G9:G99 as indicated (G8 is a header), try

=SUMPRODUCT(--(G9:G99=1),SUBTOTAL(3,OFFSET(G9,ROW(G9:G99)-ROW(G9),)))/SUBOTAL(3,G9:G99)




"tonyv" wrote:

> Hi,
>
> I'm trying to get a simple conditional formula to work when I Auto Filter.
>
> How can this be done?
>
> I need to calculate the percentage of visible cells that have 1 in them of
> the total visible cells
>
> =COUNTIF(G9:G99,1)/COUNTA(G9:G99)

Thanks for this. Yes your assumptions were correct.

Did the job once I sorted out your typo, but that was the easy bit.

"JMB" wrote:

> Assuming your data is in G9:G99 as indicated (G8 is a header), try
>
> =SUMPRODUCT(--(G9:G99=1),SUBTOTAL(3,OFFSET(G9,ROW(G9:G99)-ROW(G9),)))/SUBOTAL(3,G9:G99)
>
>
>
>
> "tonyv" wrote:
>
> > Hi,
> >
> > I'm trying to get a simple conditional formula to work when I Auto Filter.
> >
> > How can this be done?
> >
> > I need to calculate the percentage of visible cells that have 1 in them of
> > the total visible cells
> >
> > =COUNTIF(G9:G99,1)/COUNTA(G9:G99)

Thanks for the feedback - sorry about the typo.


"tonyv" wrote:

> Thanks for this. Yes your assumptions were correct.
>
> Did the job once I sorted out your typo, but that was the easy bit.
>
> "JMB" wrote:
>
> > Assuming your data is in G9:G99 as indicated (G8 is a header), try
> >
> > =SUMPRODUCT(--(G9:G99=1),SUBTOTAL(3,OFFSET(G9,ROW(G9:G99)-ROW(G9),)))/SUBOTAL(3,G9:G99)
> >
> >
> >
> >
> > "tonyv" wrote:
> >
> > > Hi,
> > >
> > > I'm trying to get a simple conditional formula to work when I Auto Filter.
> > >
> > > How can this be done?
> > >
> > > I need to calculate the percentage of visible cells that have 1 in them of
> > > the total visible cells
> > >
> > > =COUNTIF(G9:G99,1)/COUNTA(G9:G99)