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
45 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 50

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 50

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 50

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
 
LVL 1

Author Closing Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

813 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

14 Experts available now in Live!

Get 1:1 Help Now