Countif and hidden rows.

[andyw]

Hello,

Can someone help alter this formula?

At the moment the core part of it counts the cells which do not contain
the value NA

The trouble is the COUNTIF function counts the hidden cells as well.

Any suggestions (I'm quite new to excel...)

=COUNTIF(Sheet1!F14:F2126,"<>NA")

[Bob Phillips]

Hidden by what? If filtering you could try

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$13,ROW($B$14:$B$2126)-ROW($B$1),,1))*(F14:F
2126<>"NA"))

where B is th filtered column


--

HTH

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


"andyw" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Can someone help alter this formula?
>
> At the moment the core part of it counts the cells which do not contain
> the value NA
>
> The trouble is the COUNTIF function counts the hidden cells as well.
>
> Any suggestions (I'm quite new to excel...)
>
> =COUNTIF(Sheet1!F14:F2126,"<>NA")
>

[Vito]

This seems to work:

=SUBTOTAL(3,IF(SUMPRODUCT(--(F14:F2126<>"NA"))>0,F14:F2126))

[andyw]

No its not a filtered list, just a standard hidden row

[Vito]

What is the reason your rows are hidden,

Perhaps that reasoning could be turned into a condition to apply to count statement.

If there is no particular reason that can be converted to a function, then you may have to go the macro route.

[Bob Phillips]

Try this then.

First add this UDF

Function IsVisible(ByVal Target As Range)
Dim oRow As Range
Dim i As Long
Dim ary()
ReDim ary(1 To 1, 1 To Target.Rows.Count)
i = 0
For Each oRow In Target.Rows
i = i + 1
ary(1, i) = Not oRow.EntireRow.Hidden
Next oRow
IsVisible = ary
End Function


Then use this formula

=SUM(TRANSPOSE(IsVisible(F14:F26))*(F14:F26<>"NA"))

--

HTH

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


"andyw" <[email protected]> wrote in message
news:[email protected]...
>
> No its not a filtered list, just a standard hidden row
>

[Bob Phillips]

Forgot to say, the formula is an array formula, so commit with
Ctrl-Shift-Enter.

--

HTH

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


"andyw" <[email protected]> wrote in message
news:[email protected]...
>
> No its not a filtered list, just a standard hidden row
>