Countif and hidden rows.

A

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")
 
B

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)
 
V

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.
 
B

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)
 
B

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)
 
Top