COUNTIF Ignoring Hidden Rows

[Jambo2000]

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

 

[footoo]

excel countif ignore hidden rows - Google Search

 

[Chaozfate]

Hi, try this assuming your range is A:A

=SUMPRODUCT((A:A="Action")*(SUBTOTAL(103,OFFSET(A1,ROW(A:A)-MIN(ROW(A:A)),0))))

 

[Jambo2000]

excel countif ignore hidden rows - Google Search

Hi Footoo,
Thank you for your response. I did do this search and read loads of pages but just lacked the knowledge to understand

 

[Jambo2000]

Hi, try this assuming your range is A:A

=SUMPRODUCT((A:A="Action")*(SUBTOTAL(103,OFFSET(A1,ROW(A:A)-MIN(ROW(A:A)),0))))

Hi Chaozfate,

Thank you kindly. That certainly did get me closer than previously. When I entered the formula, I had a result of 1
I currently have 70 TASKS in the spread sheet and each has 10 ACTIONS. So the total visible ACTIONS is 700. All appear in Column A

Was therefore expecting a result of 700 that reduced as i hid some rows.
Tried making a few adjustments but yet again i think i just lack in knowledge to understand and adjust as required.

Any further help is appriciated

 

[Fluff]

How about
=SUMPRODUCT((LEFT(A1:A30,6)="Action")*(SUBTOTAL(103,OFFSET(A1,ROW(A1:A30)-MIN(ROW(A1:A30)),0))))

It's always best to avoid whole column references, especially in array formulae.

 

[Jambo2000]

How about
=SUMPRODUCT((LEFT(A1:A30,6)="Action")*(SUBTOTAL(103,OFFSET(A1,ROW(A1:A30)-MIN(ROW(A1:A30)),0))))

It's always best to avoid whole column references, especially in array formulae.

Hi Fluff,

With an adjustment to the row numbers that worked a treat - thank you so much!!!!
Can i assume that if i add rows i'd have to adjust that formula manually? (this was why i had been hopeful i could do an entire column)

 

[Fluff]

You can use it on an entire column, but it is likely to slow things down.
If you convert your data into a table, then you can use this

+Fluff New.xlsm
	A	B	C	D
1	Actions	Tasks		17
3	Action2
4	Action3
5	Action4
7	Action6
8	Action7
10	Action9
11	Action10
12	Action11
14	Action13
15	Action14
21	Action20
22	Action21
23	Action22
27	Action26
28	Action27
29	Action28
30	Action29
Data

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT((LEFT(Table1[Actions],6)="Action")*(SUBTOTAL(103,OFFSET(A1,ROW(Table1[Actions])-MIN(ROW(Table1[Actions])),0))))

and the range will adjust to the table.

 

[Jambo2000]

You can use it on an entire column, but it is likely to slow things down.
If you convert your data into a table, then you can use this

+Fluff New.xlsm
	A	B	C	D
1	Actions	Tasks		17
3	Action2
4	Action3
5	Action4
7	Action6
8	Action7
10	Action9
11	Action10
12	Action11
14	Action13
15	Action14
21	Action20
22	Action21
23	Action22
27	Action26
28	Action27
29	Action28
30	Action29
Data

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT((LEFT(Table1[Actions],6)="Action")*(SUBTOTAL(103,OFFSET(A1,ROW(Table1[Actions])-MIN(ROW(Table1[Actions])),0))))

and the range will adjust to the table.

Fluff,

Converting to Table didn't work and messed up all the columns and headers. Maybe there is a way to fix but such a newbie.
Would you mind letting me have a formula for the entire of column A and i can see how slow it goes

Appreciate the on going help!!! (love the profile picture BTW)

 

[Fluff]

To use full columns, just change the A1:A30 to A:A