Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2016-10-05
Medium Priority
59 Views
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
0
Question by:Alex Campbell
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 53

Expert Comment

ID: 41831057
in cell B5, try use formula:
``````=1000-5-COUNTA(INDIRECT("'"&A2&"'!A5:A1000"))
``````
and drag down the formula.

you can customize the formula accordingly.
MS2003-to-2007_b.xlsx
0

LVL 53

Expert Comment

ID: 41831060
if you want to use CountIF function, you can try this instead.
``````=1000-5-COUNTIF(INDIRECT("'"&A2&"'!A5:A1000"),"<>")
``````
0

LVL 53

Accepted Solution

Ryan Chong earned 2000 total points
ID: 41831062
sorry... for non-blank rows, simply try this:
``````=COUNTA(INDIRECT("'"&A2&"'!A5:A1000"))
``````
or
``````=COUNTIF(INDIRECT("'"&A2&"'!A5:A1000"),"<>")
``````
0

LVL 1

Author Closing Comment

ID: 41832023
Worked great, thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016