adding IF statement to subtotal for autofiltered data

Hi,

I have a column that has the autofilter applied. Each cell in the column will only consist of one letter. I can create the SUBTOTAL formula for it to count how many occupied cells there are. Now I want to count the number of times a particular letter occurs. So my original formula, which is in a cell F2 is this:

=SUBTOTAL(3,F7:F1000)

And I want to add an IF statement so it only counts the occurence of the letter "D" in the filtered data. How do I incorporate IF into the SUBTOTAL formula? Thanks in advance.

=SUMPRODUCT((SUBTOTAL(3,OFFSET($F$6,ROW($F$7:$F$1000)-ROW($F$6),,1)))*(F7:F1
000="a"))

I assume that the data is being filtered, otherwise a simpler formula is
possible.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"fbarbie" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I have a column that has the autofilter applied. Each cell in the
> column will only consist of one letter. I can create the SUBTOTAL
> formula for it to count how many occupied cells there are. Now I want
> to count the number of times a particular letter occurs. So my original
> formula, which is in a cell F2 is this:
>
> =SUBTOTAL(3,F7:F1000)
>
> And I want to add an IF statement so it only counts the occurence of
> the letter "D" in the filtered data. How do I incorporate IF into the
> SUBTOTAL formula? Thanks in advance.
>
>
> --
> fbarbie
> ------------------------------------------------------------------------
> fbarbie's Profile:
http://www.excelforum.com/member.php...fo&userid=8110
> View this thread: http://www.excelforum.com/showthread...hreadid=491566
>