Comparing visible values in two arrays
 

I'm having a problem in Excel for something that I thought would be
fairly easy to do, so I was wondering if anybody with more Excel
experience than me could help.

I have a table with four fields: Product, Sales Region, Target Sales,
Actual Sales.

I wanted a formula to calculate the proportion of all products which
hit their target. I eventually did this by using
{=SUM(IF(Dx:Dy=Cx:Cy,1,0))} and dividing by COUNT(Cx:Cy). However, I
then decided that it might be useful to find a formula which did the
same but only with visible cells, so that if I wanted to filter the
table by Sales Region, I could easily get the corresponding hit rate.

I tried using {=SUBTOTAL,(9,IF(IF(Dx:Dy=Cx:Cy,1,0)))} , but I get an
error value. Does anyone know what I'm doing wrong?

Thanks.

Comparing visible values in two arrays
 

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$20)-ROW($C$1),,1)),--($D$2:$D$2
0$C$2:$C$20))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ps.com...
I'm having a problem in Excel for something that I thought would be
fairly easy to do, so I was wondering if anybody with more Excel
experience than me could help.

I have a table with four fields: Product, Sales Region, Target Sales,
Actual Sales.

I wanted a formula to calculate the proportion of all products which
hit their target. I eventually did this by using
{=SUM(IF(Dx:Dy=Cx:Cy,1,0))} and dividing by COUNT(Cx:Cy). However, I
then decided that it might be useful to find a formula which did the
same but only with visible cells, so that if I wanted to filter the
table by Sales Region, I could easily get the corresponding hit rate.

I tried using {=SUBTOTAL,(9,IF(IF(Dx:Dy=Cx:Cy,1,0)))} , but I get an
error value. Does anyone know what I'm doing wrong?

Thanks.