?
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
4
Medium Priority
?
57 Views
Last Modified: 2016-10-06
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
Comment
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
  • Learn & ask questions
  • 3
4 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41831057
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
 
LVL 53

Expert Comment

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

Open in new window

0
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 41831062
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
 
LVL 1

Author Closing Comment

by:Alex Campbell
ID: 41832023
Worked great, thanks!
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question