Solved

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

Posted on 2014-09-25
6
150 Views
Last Modified: 2014-09-30
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
0
Comment
Question by:FFNStaff
  • 2
  • 2
  • 2
6 Comments
 
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:
=COUNTA('Tab1:Tab60'!A:A)-(2*COUNTBLANK('Tab1:Tab60'!A100000))

Let me know if that works.
-Glenn
Sent from my Windows Phone
0
 
LVL 6

Accepted Solution

by:
johnb25 earned 500 total points
ID: 40344878
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
 

Author Comment

by:FFNStaff
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!
0
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:
=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
 
LVL 6

Expert Comment

by:johnb25
ID: 40345041
Thanks Glenn
0
 

Author Comment

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

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

749 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