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

Hidden by what? If filtering you could try

=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$13,ROW($B$14:$B$2 126)-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" wrote in message
ups.com...
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")

This seems to work:

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


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489767

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

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.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489767

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" wrote in message
oups.com...

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

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" wrote in message
oups.com...

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