In Excel 2010, how do I to use countif for a number of tabs listed on the first sheet of a spreadsheet?

In the attached spreadsheet, I have a list of 42 tabs.
I want to create a formula in column b that will tell me how many non-blank rows there are in each of the tabs
from row a5 to the end of the spreadsheet. I have been using 1000 as the highest number, but I know there
are a lot fewer than that on the largest tab.
MS2003-to-2007.xlsx
LVL 1
Alex CampbellAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
sorry... for non-blank rows, simply try this:
=COUNTA(INDIRECT("'"&A2&"'!A5:A1000"))

Open in new window

or
=COUNTIF(INDIRECT("'"&A2&"'!A5:A1000"),"<>")

Open in new window

0
 
Ryan ChongCommented:
in cell B5, try use formula:
=1000-5-COUNTA(INDIRECT("'"&A2&"'!A5:A1000"))

Open in new window

and drag down the formula.

you can customize the formula accordingly.
MS2003-to-2007_b.xlsx
0
 
Ryan ChongCommented:
if you want to use CountIF function, you can try this instead.
=1000-5-COUNTIF(INDIRECT("'"&A2&"'!A5:A1000"),"<>")

Open in new window

0
 
Alex CampbellAuthor Commented:
Worked great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.