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

Posted on 2014-09-25
Last Modified: 2014-09-30

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,
Question by:FFNStaff
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
  • 2
  • 2
  • 2
LVL 27

Expert Comment

by:Glenn Ray
ID: 40344874
(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:

Let me know if that works.
Sent from my Windows Phone

Accepted Solution

johnb25 earned 500 total points
ID: 40344878

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.


Author Comment

ID: 40344945
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!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 27

Expert Comment

by:Glenn Ray
ID: 40345007
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:


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.


Expert Comment

ID: 40345041
Thanks Glenn

Author Comment

ID: 40352189
Glenn, I've reassigned the points based on your feedback.  Thank you for response.  Pat

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 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