Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Counting Non-hidden Cells In A Row

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I need to be able to count the number of cells in a row that contain numbers, but exclude the hidden columns. Here is a crude example:

Row1: 5 7 4 6 8 10 Count=6
Now I hide two rows
Row1: 5 7 8 10 Count=4

Any help is appreciated but no macros please. I need this to be as user friendly as possible. I also know that SUBTOTAL works great with this when the data is in a column but it doesn't want to work when I use it on a row like the example.

View Answers     

Similar Excel Tutorials

Delete Hidden Rows in a Workbook
This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only d ...
Delete Hidden Worksheets
This macro will delete all hidden worksheets within a workbook. When you run this macro a warning window will pop ...
Chart Hidden Data in Excel
How to show data from hidden rows and columns on a chart in Excel. When you hide rows or columns of data, a chart ...
Count the Number of Cells that Start or End with Specific Text in Excel
How to count cells that match text at the start or the end of a string in Excel. If you want a fuller explanation o ...

Helpful Excel Macros

Count The Number of Words in a Cell or Range of Cells in Excel - UDF
- Count words in cells with this user defined function (UDF). This UDF allows you to count the number of words that are w
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics

I (a code witing novice) am trying to programatically add the values from a user form as a new row (record) in the Worksheet CSData. Each time the user form is completed and the cmd button "Save Entry" is clicked I need it to append the data into the next available row of the worksheet. I've searching through the forum for examples of how to do this and found what I thought might work. When I tried to use the attached code it broke on the first line after "With rng" .
Any help would be much apprciated!


Private Sub cmdSaveEntry_Click()
    Dim rng As Range
    Dim Row1 As Long
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("SCData")
    With ws1
        Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            With rng
                ws1.Cells(Row1, 1).Value = Me.cboCity.Value
                ws1.Cells(Row1, 2).Value = Me.txtFlightNo.Value
                ws1.Cells(Row1, 3).Value = Me.txtDate.Value
                ws1.Cells(Row1, 4).Value = Me.txtC1.Value
                ws1.Cells(Row1, 5).Value = Me.txtD1.Value
                ws1.Cells(Row1, 6).Value = Me.txtC2.Value
                ws1.Cells(Row1, 7).Value = Me.txtD2.Value
            End With
    End With
End Sub

(See attached file for a clearer understanding)

I currently have two rows of data. I have used the SUMPRODUCT function to count the number of times that the value in the second row is less than the number directly above it in the first row.

There are 3 possible numbers in the first row: 3, 4, or 5.
I need to split these up so that I can have these three seperate categories.

When a value row1 = 3, I need to count the number of times that the number directly below it in row 2 is < 3.

When a value row1 = 4, I need to count the number of times that the number directly below it in row 2 is < 4.

Same deal with 5.

I have tried using an IF statement for when the value is 3:

But this doesn't work.

Any help is appreciated.

Is there a shorter code I can use to hide/unhide rows with a VBA button?

There are no "fixed" range number of rows between Header to Header; they are random. Example: There are less rows between Header 1 to Header 2 then there are rows between Header 2 to Header 3, etc.

Header 1 at (A2:K2)

range: ( A4 : K190 )

***hide every 6 rows*** (A4 :K9), (A11:K16),(A18:K23), etc.

***then hide 5 rows range*** (A186: K190 )

Header 2 at (A191:K191)

range: ( A193 : K386 )

***hide every 6 rows, range:*** ( A193 :K198), (A200:K205),(A207:K212), etc.

***then hide 5 rows range*** (A382: K386 )

Header 3 at (A387:K387)

range: ( A389 : K582 )

***hide every 6 rows*** ( A389 :K394), (A396:K401), (A403:K408), etc.

***then hide 5 rows range*** (A578: K582 )

Header 4 at (A583:K583)

range: ( A585 : K701 )

***hide every 6 rows*** ( A585 :K589), (A592:K597), (A599:K604), etc.

***then hide 5 rows range*** (A697: K701 )

Here is the code I used, but I had to type in every range for it to work correctly.


Sub Hidden()
Application.ScreenUpdating = False
Rows("4:9").Hidden = Not Rows("4:9").Hidden
Rows("11:16").Hidden = Not Rows("11:16").Hidden
Rows("18:23").Hidden = Not Rows("18:23").Hidden
Rows("25:30").Hidden = Not Rows("25:30").Hidden
Rows("32:37").Hidden = Not Rows("32:37").Hidden
Rows("39:44").Hidden = Not Rows("39:44").Hidden
Rows("46:51").Hidden = Not Rows("46:51").Hidden
Rows("53:58").Hidden = Not Rows("53:58").Hidden
Rows("60:65").Hidden = Not Rows("60:65").Hidden
Rows("67:72").Hidden = Not Rows("67:72").Hidden
Rows("74:79").Hidden = Not Rows("74:79").Hidden
Rows("81:86").Hidden = Not Rows("81:86").Hidden
Rows("88:93").Hidden = Not Rows("88:93").Hidden
Rows("95:100").Hidden = Not Rows("95:100").Hidden
Rows("102:107").Hidden = Not Rows("102:107").Hidden
Rows("109:114").Hidden = Not Rows("109:114").Hidden
Rows("116:121").Hidden = Not Rows("116:121").Hidden
Rows("123:128").Hidden = Not Rows("123:128").Hidden
Rows("130:135").Hidden = Not Rows("130:135").Hidden
Rows("137:142").Hidden = Not Rows("137:142").Hidden
Rows("144:149").Hidden = Not Rows("144:149").Hidden
Rows("151:156").Hidden = Not Rows("151:156").Hidden
Rows("158:163").Hidden = Not Rows("158:163").Hidden
Rows("165:170").Hidden = Not Rows("165:170").Hidden
Rows("172:177").Hidden = Not Rows("172:177").Hidden
Rows("179:184").Hidden = Not Rows("179:184").Hidden
Rows("186:190").Hidden = Not Rows("186:190").Hidden
Rows("193:198").Hidden = Not Rows("193:198").Hidden
Rows("200:205").Hidden = Not Rows("200:205").Hidden
Rows("207:212").Hidden = Not Rows("207:212").Hidden
Rows("214:219").Hidden = Not Rows("214:219").Hidden
Rows("221:226").Hidden = Not Rows("221:226").Hidden
Rows("228:233").Hidden = Not Rows("228:233").Hidden
Rows("235:240").Hidden = Not Rows("235:240").Hidden
Rows("242:247").Hidden = Not Rows("242:247").Hidden
Rows("249:254").Hidden = Not Rows("249:254").Hidden
Rows("256:261").Hidden = Not Rows("256:261").Hidden
Rows("263:268").Hidden = Not Rows("263:268").Hidden
Rows("270:275").Hidden = Not Rows("270:275").Hidden
Rows("277:282").Hidden = Not Rows("277:282").Hidden
Rows("284:289").Hidden = Not Rows("284:289").Hidden
Rows("291:296").Hidden = Not Rows("291:296").Hidden
Rows("298:303").Hidden = Not Rows("298:303").Hidden
Rows("305:310").Hidden = Not Rows("305:310").Hidden
Rows("312:317").Hidden = Not Rows("312:317").Hidden
Rows("319:324").Hidden = Not Rows("319:324").Hidden
Rows("326:331").Hidden = Not Rows("326:331").Hidden
Rows("333:338").Hidden = Not Rows("333:338").Hidden
Rows("340:345").Hidden = Not Rows("340:345").Hidden
Rows("347:352").Hidden = Not Rows("347:352").Hidden
Rows("354:359").Hidden = Not Rows("354:359").Hidden
Rows("361:366").Hidden = Not Rows("361:366").Hidden
Rows("368:373").Hidden = Not Rows("368:373").Hidden
Rows("375:380").Hidden = Not Rows("375:380").Hidden
Rows("382:386").Hidden = Not Rows("382:386").Hidden
Rows("389:394").Hidden = Not Rows("389:394").Hidden
Rows("396:401").Hidden = Not Rows("396:401").Hidden
Rows("403:408").Hidden = Not Rows("403:408").Hidden
Rows("410:415").Hidden = Not Rows("410:415").Hidden
Rows("417:422").Hidden = Not Rows("417:422").Hidden
Rows("424:429").Hidden = Not Rows("424:429").Hidden
Rows("431:436").Hidden = Not Rows("431:436").Hidden
Rows("438:443").Hidden = Not Rows("438:443").Hidden
Rows("445:450").Hidden = Not Rows("445:450").Hidden
Rows("452:457").Hidden = Not Rows("452:457").Hidden
Rows("459:464").Hidden = Not Rows("459:464").Hidden
Rows("466:471").Hidden = Not Rows("466:471").Hidden
Rows("473:478").Hidden = Not Rows("473:478").Hidden
Rows("480:485").Hidden = Not Rows("480:485").Hidden
Rows("487:492").Hidden = Not Rows("487:492").Hidden
Rows("494:499").Hidden = Not Rows("494:499").Hidden
Rows("501:506").Hidden = Not Rows("501:506").Hidden
Rows("508:513").Hidden = Not Rows("508:513").Hidden
Rows("515:520").Hidden = Not Rows("515:520").Hidden
Rows("522:527").Hidden = Not Rows("522:527").Hidden
Rows("529:534").Hidden = Not Rows("529:534").Hidden
Rows("536:541").Hidden = Not Rows("536:541").Hidden
Rows("543:548").Hidden = Not Rows("543:548").Hidden
Rows("550:555").Hidden = Not Rows("550:555").Hidden
Rows("557:562").Hidden = Not Rows("557:562").Hidden
Rows("564:569").Hidden = Not Rows("564:569").Hidden
Rows("571:576").Hidden = Not Rows("571:576").Hidden
Rows("578:582").Hidden = Not Rows("578:582").Hidden
Rows("585:590").Hidden = Not Rows("585:590").Hidden
Rows("592:597").Hidden = Not Rows("592:597").Hidden
Rows("599:604").Hidden = Not Rows("599:604").Hidden
Rows("606:611").Hidden = Not Rows("606:611").Hidden
Rows("613:618").Hidden = Not Rows("613:618").Hidden
Rows("620:625").Hidden = Not Rows("620:625").Hidden
Rows("627:632").Hidden = Not Rows("627:632").Hidden
Rows("634:639").Hidden = Not Rows("634:639").Hidden
Rows("641:646").Hidden = Not Rows("641:646").Hidden
Rows("648:653").Hidden = Not Rows("648:653").Hidden
Rows("655:660").Hidden = Not Rows("655:660").Hidden
Rows("662:667").Hidden = Not Rows("662:667").Hidden
Rows("669:674").Hidden = Not Rows("669:674").Hidden
Rows("676:681").Hidden = Not Rows("676:681").Hidden
Rows("683:688").Hidden = Not Rows("683:688").Hidden
Rows("690:695").Hidden = Not Rows("690:695").Hidden
Rows("697:701").Hidden = Not Rows("697:701").Hidden
Application.ScreenUpdating = True
End Sub

Excel 2007 and Windows XP Professional


I was wondering if was possible to create a conditional calculation excluding hidden columns.

For example in C4:HA4 the cells may have a number of values L, S, U etc.

When a user access the spreadsheet certain columns a hidden based on comparing their PC user ID to an access list in the workbook.

What I would like to be able to do would be to count the instances of L in C4:HA4 but exluding the hidden columns.

I'd hoped I might be able to use SUBTOTAL in a similar way to Domenic came up with he


First off, I'm new to this forum as well to using Macros / VBA.

I've created an excel spreadsheet, and I want to hide rows where the value of a specified column = 0.

I can do this easily with the AutoFilter features, unfortunately, it leaves the pulldown arrows and this screws up any printing of the spreadsheet.

I've created two basic VBA subroutines that do the same thing, but I want to assign these to two buttons I can put on the spreadsheet so other users can also Hide / Unhide the data as needed.

My subroutines a

Sub hide()
Dim count As Integer
For count = 5 To 125
If Cells(count, 11).Value < 1 Then
Rows(count).Hidden = True
End If
End Sub

Sub unhide()
Dim count As Integer
For count = 5 To 125
If Cells(count, 11).Value < 1 Then
Rows(count).Hidden = False
End If
End Sub

As you can see, very basic. I can't seem to find a way to assign these or call these routines via a button in the spreadsheet that other users can activate.

Any help would be appreciated, and again, thanks in advance,


When using auto filters. (1) Date to Date then (2) Product ID I show visible rows that I can total the columns (Total Weight) that excludes non visible lines using =SUBTOTAL(9,Q10:Q1000). Works.

Additionally I need to count ONLY the number of visible cells in (Column P) the visible columns that contain the text of various Truck Companies to get a count of the number of trucks shipped. If I try to count the non blank cells of the visible cells it counts all the hidden cells too. How do I count just the number of visible cells containing text in a column when in auto filter? OR count someother column containing numbers or text for the purpose of getting a count of the visible lines that would get the same result (number of shipments)?

Hi. I want columns on one of my sheets to hide starting with the first blank one until the rest are hidden. I got my rows to hide in the same fashion with the following:


FirstBlankRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range(Cells(FirstBlankRow, "A"), Cells(Rows.Count, "A")).EntireRow.Hidden = True

But I couldn't get a version working for the columns. Any help would be appreciated!


I need some vba code to
1)count along a row the number of VISIBLE populated cells and
2) If the number of visible cells is = 13 then it will
3)Hide the 2nd columns data
I tried using this just for the count mechanism and testing by manually hiding the 2nd column, Range(b:b) at that time but the count cannot pass the hidden cell despite the special cells code.

so at the moment i am playing around with the following


ColcCount = Selection.SpecialCells(xlCellTypeVisible).Columns.Count 
If ColCount = "13" Then 
    Range("A1").Offset(0, 1).Select 
    Selection.EntireColumn.Hidden = True 
End If 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

The logic seems right, but its probably something really really simple for you guys!!!
Thanks in advance

When using auto filters. Sorting by (1) Date to Date then by (2) Product ID, I show visible rows that I can total the columns (Total Weight) that excludes non visible lines using =SUBTOTAL(9,Q10:Q1000). That Works.

But additionally I need to count ONLY the number of items in the visible column that contain the text of various Truck Companies to get a count of the number of trucks shipped. If I try to count the non blank cells of the visible cells it counts all the hidden cells too.

How do I count just the number of visible cells containing text in a column when in auto filter? OR count someother column containing numbers or text for the purpose of getting a count of the visible lines that would get the same result (number of shipments)?

I have a worksheet that populates cells via a VLOOKUP function.

On each column I use the SUBTOTAL function to count the number of rows with data in them =SUBTOTAL(3,A1:A1000)

The VLOOKUP function returns "0" if a field is blank in the source data and this is being counted by the SUBTOTAL function.

Is there a way I can count the rows and exclude anything with "0" in it? Note that the cells contain numbers and text so I need to exclude "0" only.

Hi: I need to hide an entire row if cell values in two non-adjacent columns are zero.

Worksheet has data in Columns A through L

Columns E and L contain dollar figures in rows 52 through 77. The cells are pre-loaded with zero dollars with anticipation that some of the zeros will be changed by the user.

If BOTH the values in Column E & L remain as Zero, I want to Hide the entire row. The Macro below stops working after it looks at the first row and will not continue to loop.

Any ideas? I think my If then statement is wrong???

Sub HideRows()
With Range("E52:E57", "L52:L57")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

Note that if I were only looking at Column E without a need to column L, the above macro will work with a change to the Range at ("E52:E77"), but when I ask Excel to analyze Two Columns, E & L, or two values in the same row, I have the issue.

Help is appreciated

Hi, i am trying to understand a macro i used previously and try and update the macro so i can use it now.

what this macro did, was to look at cells in row 1, and if the cell value = "Hide" the the macro would hide the entire column.

Sub Hide_Columns()
Dim a As Long, c As Long, d As Long, e As Long
Dim b As String, f As String
    For a = 1 To Sheets.Count
    b = Worksheets(a).Name
        If b = "Overview" Or b = "Sheet1" Or b = "Sheet2" Then
        c = Worksheets(a).Cells(1, Columns.Count).End(xlToLeft).Column
        d = Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row
            For e = 1 To c
                If Worksheets(a).Cells(1, e) = "Hide" Then
              f = Chr(e + 64)
                Worksheets(a).Range(f & 1 & ":" & f & d).EntireColumn.Hidden = True
                End If
            Next e
        End If
    Next a
MsgBox "Completed"
End Sub

What I am looking at now, is changing this macro to look at all cells in row 4, from column A to column BZ, and if the cell value = "Hide", then the entire column will be hidden.

I can't figure out which bits of the macro are doing what. Any help would be greatly appreciated.



I am trying to figure out if a hidden group of cells can be excluded from a formula.

Basically, I have a column which counts incidents each month.

So A1 - A50 has a total count of 50.

A1 - A25 is March, and A26 - A50 is April. So if I hide A1-A25, would it be posible to have my count formula to update automatically to remove the hidden cells?

This would help me so that I could just add data each month, instead of having to copy paste and delete. + I could run reports for the whole year when needed.

Kind Regards


Hi guys,

So this is the new problem I am faced with. I work with Excel 2003. I want to do a formula that works through filters, so I did subtotal (e.g =SUBTOTAL(103,E$9:E$301) ) but, in the column that I need to count is a formula entered in each cell which is an IF/AND formula =IF(AND(F9="",D9<TODAY(),ISNUMBER(D9)),"Over due","") so the subtotal is counting all of the cells when I only need it to count the cells which display the result "over due", then I need it to work in a filter...

I can not think of a way that this is possible...then again my knowledge of Excel is basic? I can do COUNTIF (which is what I have now) but then it doesn't work in filters.

If it isn't possible then never mind I'll work round it, but I would prefer to avoid VBA where possible.


Here's the problem:
1 a column of NON-contiguous rows (sort/hide the rest).
2 Autosum and subtotal(109 or 9, work as expected.
2 Average doesn't work and neither does subtotal(101 or 1,...

The problem is that an average is the sum divided by the count.
But the count may NOT be augmented by any row/cell that contains a
zero !

For example: I have a column of 20 numbers whose total = 1,000
If all cells contained a positive value, the average would equal 50.

If 10 of the cells contain a ZERO ("0") then the average is now 100:
1,000 / 10 = 100.

Unfortunately, excel's functions (average or subtotal(109,...) still produce a result of 50 !

How do I get excel to produce the correct average of 100 for 20 cells whose sum of their contents = 1,000 but 10 of the cells have a zero value?

This is a productivity issue wherein cells that contain a zero means the employee was absent for any number of legit reasons. Thus the "average" I'm seeking is the average quantity of work performed on the days the employee was actually working.

BTW: the cells are in NON-contiguous rows.

I have a Workshet that contains a Checkbox from the Control Toolbar. When the Checkbox is checked, non-adjacent rows are un-hidden, and when the checkbox is unchecked, the non-adjacent rows are hidden.

The rows in question contain buttons that activate other macros. The Problem I have is that when the Checkbox is unchecked and the rows in question are hidden, the buttons continue to show . I need a macros to hide the buttons when the rows that sit under the buttons are also hdden, i.e., a button on top of row 3 should also be hidden when row 3 is hidden.

The buttons are clipart from the AutoShapes Toolbar.

My Macros is below. Each of the rows that are hidden/unhidden by the checkbox contain 2 additional buttons that should be hidden when the rows are hidden.

Private Sub CheckBox1_Click()
If Sheet1.CheckBox1 Then Rows("3").Hidden = False
Rows("18").Hidden = False
Rows("169").Hidden = False
Rows("208").Hidden = False
Rows("216").Hidden = False
Rows("255").Hidden = False
Else Rows("3").Hidden = True
Rows("18").Hidden = True
Rows("169").Hidden = True
Rows("208").Hidden = True
Rows("216").Hidden = True
Rows("255").Hidden = True
End If
End Sub

Help is appreciated!

Hi, I find SUBTOTAL function works fantastically with hidden rows, however, it does not seem to work with hidden columns. Having read a bit more about this useful, function I don't understand why Microsoft does not implement the same for it to work with hidden columns (horizontally). Is it possible to create a UDF to accomplish this function just like it works with hidden rows? If so, please could you help me out on this. Thanks

Hello! I am new here. I have two macros.
1. This Macro Hides all columns whose first entry is "N" and shows all columns whose first entry is "Y".
2. The another Macro unhides all columns i.e. entire data is shown.

I need to modify these two macros to the following (or create new ones)
1. The first macro should hide all columns whose first entry is "N" and shows all columns whose first entry is Y and hides those rows which contains "NO" in this column (whose first entry is Y).
2. This macro should unhide all rows and columns i.e. entire data is shown.

The Macro code is:

Option Explicit

Sub hideCols()
Dim cl As Range
Dim rng As Range
With Worksheets(1)
Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlToRight))
For Each cl In rng
If cl.Text = "N" Then cl.EntireColumn.Hidden = True
Next cl
End With
End Sub
Sub showCols()
Dim col As Long
Dim cnt As Long

cnt = Worksheets(1).UsedRange.Columns.Count

For col = 1 To cnt
If Columns(col).EntireColumn.Hidden = True Then Columns(col).EntireColumn.Hidden = False
End Sub

I hope to get some help from experts. I will be really thankful if I get some help.

I have two listbox on userform. On first I show columns that are not hidden, and on second that are hidden. It works ok when I open form, but it doesn't work when I click from last to first and it does't show all hidden on other listbox. Expert help pls.


Private Sub UserForm_Initialize()
Dim last As Integer
With Sheets("KUMULATIVNA")
        last = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set rng = Range(Cells(1, 1), Cells(1, last))
End With
With Me.ListBox2
    .ColumnCount = 2
For i = 1 To last Step 1
        If rng.Cells(1, i).Width  0 Then 
            .AddItem rng.Cells(1, i).Value
            .List(.ListCount - 1, 1) = rng.Cells(1, i).Column
        End If
Next i
End With
With Me.ListBox3
    .ColumnCount = 2
For i = 1 To last Step 1
      If rng.Cells(1, i).Width = 0 Then 
            .AddItem rng.Cells(1, i).Value
            .List(.ListCount - 1, 1) = rng.Cells(1, i).column
      End If
Next i
End With
End Sub
Private Sub ListBox2_Click()
Dim last As Integer

With Sheets("KUMULATIVNA")
        last = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

Set rng = Range(Cells(1, 1), Cells(1, last))

For i = 1 To last Step 1
        If rng.Cells(1, i) = ListBox2.Value Then 
           Columns(i).Hidden = True
        End If
Next i

With Sheets("KUMULATIVNA")
        last = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set rng = Range(Cells(1, 1), Cells(1, last))
End With
With Me.ListBox2
    .ColumnCount = 2
For i = 1 To last Step 1
        If rng.Cells(1, i).Width  0 Then 
            .AddItem rng.Cells(1, i).Value
            .List(.ListCount - 1, 1) = rng.Cells(1, i).Column
        End If
Next i
End With

With Me.ListBox3
    .ColumnCount = 2
    For i = 1 To last Step 1
       If rng.Cells(1, i).Width = 0 Then 
            .AddItem rng.Cells(1, i).Value
            .List(.ListCount - 1, 1) = rng.Cells(1, i).Column
       End If
   Next i
End With
End Sub

I need help in using a subtotal formula by putting a condition to it.

I would like to know if:

=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.

I have attached a worksheet with the example.

In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count.
When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is $1050 in cell C99.
Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.
Your help will be highly appreciated.
Thank You,

Hi All,

I'd really appreciate some help with a problem that I just cannot solve


Row1......2......6......4......2......4......3......0......0......5.....This row has random numbers
Row2......2.....8......12.....14....18.....21..#N/A..#N/A.. ?? (26)...This row sums previous number (Row2)+number above (Row1)=cumulative total

problem... being what formula can I put in the ?? column to pick up the cumulative count?...

When there is nothing to add to the cumulative total, I am telling Row2 to = #N/A because I need to chart Row2 and I don't want to chart 0's or repeat numbers

....can anyone please offer a non-VB solution to 'picking-up' where the count was lost...?

Many thanks in advance



I am trying to write a macro and i am having trouble hgetting it right. I have large amount of data in columns and what I would like to do is the

1. First I need to find the first blank cell in that row.

2. After finding the blank cell, I would like to compare the value in 3rd column from the blank cell( For example if the last blank cell is Row1,ColumnR then I need to Compare Row1,ColumnO) with Column E ( Is always Column E).

3. Based on above example if Row1,ColumnO>Row1,ColumnE, then goto next row else Row1,ColumnR value should be Row1,ColumnE and I would like to get Row1,ColumnS and Row1,ColumnT vaues to be same as last 2 columns from the blank cell which was determined first (i.e., from above example Row1,ColumnS and Row1,ColumnT vaues to be same as Row1,ColumnP and Row1,ColumnQ).

4.I would like to perform the above procedure for all the rows in the worksheet and the blank cell may be anywhere in the column for that particular row.

I don't know whetehr it is possible to write a macro to perform above procedure or i need to do that manually which i hate as i have large amount of data.

Thanks in Advance.

Ladies and gentlemen. I would like to be assisted what formalue I can use for linking cells in one column in a sheet to cells in a row in another sheet. for example I have sheet1 column A 1:1000 to be linked to sheet 2 row1 A:ALM. So that when ever data in sheet1column A1:1000 changes data in sheet2 row1 A:ALM also changes. Eg if I change data in column A cell 1 data in row1 cell A also changes, ColumnA cell 2 changes row1 column B (mind you two different sheet in one work book.

I have people's names in Cells A9 to A42 and I want to count the number of unique people in the list so I can multiply the number of unique people by 40 and give me the total # of hours I have available in a week given a 40 hour work week.

I came up with the following formula which works great except the name column is filtered so when I select a filter value I get the same number no matter what the filter is set to.

=SUM(IF(FREQUENCY(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""), IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))>0,1,0))

So I tried the following:
=SUBTOTAL(9,(IF(FREQUENCY(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""), IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))>0,1,0)))

This doesn't work because I don't think the subtotal function works with an array (not sure about that though).

I also tried something like this
=SUM(IF(FREQUENCY(IF(LEN(A9:A42)>0,(IF(SUBTOTAL(3,OFFSET(A9,ROW(A9:A42)-ROW(A9),0)),MATCH(A9:A42,A9:A42,0),)),""),(IF(LEN(A9:A42)>0,MATCH(A9:A42,A9:A42,0),""))),1)) and it seemed to work but if the filter value doesn't have the first person in my rows as part of the selection it always comes back with a number 1 higher than it should.

I'm stumped. Is there a way to change the array to a list so the first subtotal function I posted will work or is there a better way altogether to accomplish what I want?

Thank you

I use the subtotal function a lot, but it seems to be very limited. I have read other post on this subject but none seem to be able to help. Is there anyway to create a custom formula in the subtotal function??
I am using Subtotal to provide me with a count and sum of each group (created by subtotal -group doesn't work as my data is mostly text). I am trying to create a conditional formula that will divide the sum figure by the count - not so difficult - but I am wanting this to re-occur for each subtotal however, each group has varying numbers of rows hence the use of count. Any assistance with this or a macro that will perform this function would be greatly appreciated

Thanks in advance