Countif and hidden rows.
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") |
Countif and hidden rows.
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") |
Countif and hidden rows.
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 |
Countif and hidden rows.
No its not a filtered list, just a standard hidden row |
Countif and hidden rows.
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 |
Countif and hidden rows.
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 |
Countif and hidden rows.
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 |
All times are GMT +1. The time now is 08:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com