How do I total counts on multiple tabs in a workbook in Excel 2010?

Hi,

I have a workbook with 60+ tabs.  Each tab has been subtotaled to Count account numbers in column A.  In a master tab, I would like to total all the counts into one cell.  Is there a formula that can do this without using Consolidate and creating 60+ ranges?  The counts in the various sheets can range from 1 to several thousand so the Grand Counts are in different rows.  

I've tried Sumif using tab 2:tab 60 but that didn't work.  

Suggestions would be greatly appreciated.

Thank you,
Pat
FFNStaffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
(I'm mobile now, so this is a preliminary guess.)

If column A contains only
- a header label (ex., "account")
- account names
- a count function at the bottom that shows the number of accounts.

then a 3D function should work with some adjustment:
=COUNTA('Tab1:Tab60'!A:A)-(2*COUNTBLANK('Tab1:Tab60'!A100000))

Let me know if that works.
-Glenn
Sent from my Windows Phone
0
johnb25Commented:
Pat,

You could redo the counts on the same row of all books, below the last possible row of accounts,  and then sum the totals, e.g. my small example
=COUNTA(A1:A19)-1   this in Row 20 of all books. (-1 to exclude existing total) Just group the sheets before entering to add to all.
And then this in your top sheet: =SUM(Sheet2:Sheet3!A20).

You cannot use 3D referencing with Sumif.

John
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FFNStaffAuthor Commented:
John, Glenn,

After sending this I realized the count wasn't giving me a count of unique account numbers, which is what I need.  So I used a frequency formula that I found and pasted into all the sheets and totaled it as John suggested.  Thank you both for responding so quickly!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Glenn RayExcel VBA DeveloperCommented:
My solution is not correct. :-(  

I *hoped* that the COUNTBLANK function could use 3D referencing to produce the number of sheets in a 3D range.  But it, nor COUNTIF, will produce the a valid result (error).

It is possible to produce the number of sheets with an array formula:
=SUM(N(NOT(ISERROR(N(INDIRECT("Tab1"&ROW($B$1:$B$70)&"!$AA$1"))))))

and
=COUNTA('Tab1:Tab60'!A:A)-(2*arrayresult)

but that's a lot of work.   If you *knew* the number of sheets, you could just plug that in.

I think johnb25 should get all the points here.

-Glenn
0
johnb25Commented:
Thanks Glenn
0
FFNStaffAuthor Commented:
Glenn, I've reassigned the points based on your feedback.  Thank you for response.  Pat
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.