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
34 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:Alex972
  • 3
4 Comments
 
LVL 49

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 49

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 49

Accepted Solution

by:
Ryan Chong earned 500 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
 

Author Closing Comment

by:Alex972
ID: 41832023
Worked great, thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now