COUNTIF Ignoring Hidden Rows

Jambo2000

New Member
Joined
Jul 17, 2020
Messages
10
Platform
  1. Windows
Very New to Excel - Please go easy on me ;)

I'm putting together a spreadsheet to store and monitor TASKS
Each TASK will have smaller ACTIONS to go along with it. I have set the sheet so that once all the ACTIONS are 100% done, the TASK is also marked as 100% done.
I've created a tally at the top for both the total TASKS and the total ACTIONS.

Each TASK has been populated with 10 rows of ACTIONS.
As not every TASK will need all 10 ACTIONS, the rows not used will be hidden. Issue i have is the formula i'm using to count the total ACTIONS is : =COUNTIF(A7:A1000,"Action*")
This counts the total even if the row is hidden. Looking around on Google, you can't seem to have something that both ignores the hidden rows and uses the COUNTIF

Struggling to find a work around - Please help
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,629
Office Version
  1. 365
Platform
  1. Windows
Struggling to find a work around - Please help
A helper column is another way, far more efficient that arrays, especially with full columns. Assuming column B is empty (if not use any empty column).

In B7, fill down to end of data.
=SUBTOTAL(3,A7)

Then you can use =COUNTIFS(A:A,"*ACTION*",B:B,1) to get your count.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Jambo2000

New Member
Joined
Jul 17, 2020
Messages
10
Platform
  1. Windows
To use full columns, just change the A1:A30 to A:A


Didn't work but the previous formula updates as i add additional rows so i can live with that.
Thank you again. Been driving me mad for days.

Now onto trying to work out ActiveX controls to copy & paste rows at the click of a button, with the formulas included......wish me luck ;)
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,161
Office Version
  1. 2016
Platform
  1. Windows
copy & paste rows at the click of a button, with the formulas included
Try this to add a row immediately below the last cell with data in column A :
VBA Code:
Sub Add_Row()
With Cells(Rows.Count, 1).End(3).EntireRow
    .Copy .Offset(1)
    .Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub
 

Jambo2000

New Member
Joined
Jul 17, 2020
Messages
10
Platform
  1. Windows
Try this to add a row immediately below the last cell with data in column A :
VBA Code:
Sub Add_Row()
With Cells(Rows.Count, 1).End(3).EntireRow
    .Copy .Offset(1)
    .Offset(1).SpecialCells(xlCellTypeConstants).ClearContents
End With
End Sub


Footoo,

I was fooling around with it earlier to try and understand it. I used this:

Private Sub CommandButton1_Click()
Range("6:16").Copy Range("894:904")
End Sub

This did add in just what i wanted but as i need to be able to do this multiple times and miss a row each time, i need to do further work on it
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,161
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You need to explain exactly what you are trying to do. Some sample data might help.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,161
Office Version
  1. 2016
Platform
  1. Windows
Perhaps this ? :
VBA Code:
Sub Rws()
Rows("6:16").Copy Cells(Rows.Count, 1).End(3)(3)
End Sub
 

Jambo2000

New Member
Joined
Jul 17, 2020
Messages
10
Platform
  1. Windows

ADVERTISEMENT

Perhaps this ? :
VBA Code:
Sub Rws()
Rows("6:16").Copy Cells(Rows.Count, 1).End(3)(3)
End Sub

So I've got 74 TASKS entered, each with 10 ACTIONS. As the ACTIONS are part or fully completed, it populates the percentage of the TASK completed. All of which is recorded in a tally at the top left. This all stays on screen at the top as you scroll down.
The Add TASK button currently when pressed, copies all the rows from TASK 1 and adds them at the bottom in specific rows. Using this code

Private Sub CommandButton1_Click()
Range("6:16").Copy Range("894:904")
End Sub

I'd like to be able to click this button multiple times and each time a new TASK is added at the bottom leaving 1 line between it and the previous TASK

Hope i have explained this ok, don't know the terms for things i'm sorry
 

Attachments

  • Screen Grab.JPG
    Screen Grab.JPG
    89.7 KB · Views: 3

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,161
Office Version
  1. 2016
Platform
  1. Windows
Have you tried the macro?
Try it first then post what else it should do.
How do the data in C3:D4 get entered?
 

Jambo2000

New Member
Joined
Jul 17, 2020
Messages
10
Platform
  1. Windows
Have you tried the macro?
Try it first then post what else it should do.
How do the data in C3:D4 get entered?

Tried the last code ytou suggested and this didn't do anything when i clicked the button. The current code, shown here:

Private Sub CommandButton1_Click()
Range("6:16").Copy Range("894:904")
End Sub


gets me the closest but is only a single use.

C3 and C4 are both populated with
=COUNTIF formulas
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,161
Office Version
  1. 2016
Platform
  1. Windows
What did you try - like this ? :
VBA Code:
Private Sub CommandButton1_Click()
Rows("6:16").Copy Cells(Rows.Count, 1).End(3)(3)
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,183
Messages
5,576,575
Members
412,735
Latest member
jwoods1204
Top