Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-09-25
6
Medium Priority
?
156 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

926 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