AuditBible
January 23, 2018, 04:09:09 AM *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
Sirius Satellite Radio Inc.
 
   Home   News Alerts Help Search Calendar Members Contributions Staff List Login Register  

Microsoft Store
Del.icio.us Digg FURL FaceBook Stumble Upon Reddit SlashDot Google Bookmarks

Pages: 1   Go Down
  Add bookmark  |  Print  
Author Topic: Creating a total page & totalling cells across a workbook  (Read 4499 times)
 
0 Members and 1 Guest are viewing this topic.
beancounter
Administrator
*****
Offline Offline

Gender: Male
Experience: 1985
Posts: 168



Activity
100%


View Profile WWW
« on: July 02, 2008, 10:18:02 AM »

...ever have an insured provide you with an Excel workbook where each month is summarized on its own sheet...and there's no total page summarizing all the months for the policy period?  Wouldn't it be nice if there was some way you could easily obtain a summary total page for this 12-sheet workbook?

Well, there is!  However, this will only work if the insured's summary maintains a consistent format from month-to-month.  It seems this is frequently not the case, however, since so few people who use Excel actually understand its capabilities (including me), but maybe with a little tweaking you can insert a row here...and delete a column there...to get it so that all the pertinent data are located in the same cells accross the workbook (example: 8810 Gross Pay is always in cell B2 on each sheet, 8742 is always in B3, etc.).

Step 1 - copy an existing sheet by selecting "Move or Copy sheet..." from the Edit menu which brings up the Move or Copy command box (alternately, you can right-click the tab of the sheet).  Be sure to check the "Create a copy" box in the lower left and select the location where you want your new sheet copied to in the workbook, otherwise, you'll just end-up moving the existing sheet.

Step 2 - Name your new sheet "Totals" or something (right-click on the tab and select "Rename" or just double-click on the tab itself and rename the tab).  Now you have a new sheet formatted just like all the others in the workbook.  Let's assume you have 12 sheets and each is named "Jan", "Feb"...on to "Dec".

Step 3 - Let's say Column A lists all the codes for the policy you're auditing, and Column G lists the chargeable payroll corresponding to each code.  If code 8810 is in Row 10, enter this formula to total the chargeable payroll for code 8810 for all 12-months:
=SUM('Jan:Dec'!B2)
Assuming the format is identical for all 12 sheets, you'll have the total chargeable 8810 payroll for all 12-months in cell G10 now.  Simply copy the formula down the column as needed for the other codes.

Alternately,

1. Select a cell in your summary sheet where you want the data and and type =SUM(.
2. Select the tab for the first sheet, Jan.
3. Hold the Shift key, and then select the tab for the last sheet, Dec.
4. Select the cell you wish to total (example, B2), and then press Enter.

The formula is now =SUM('Jan:Dec'!B2).
« Last Edit: August 02, 2011, 11:37:59 AM by beancounter, Reason: added 4 step summary » Logged

bc
beancounter
Administrator
*****
Offline Offline

Gender: Male
Experience: 1985
Posts: 168



Activity
100%


View Profile WWW
« Reply #1 on: October 12, 2011, 10:11:00 PM »

I had one today where the insured dropped terminated employees Sad

I copied the last sheet to create my total page and inserted the terminated employees' names in column A along with the active employees' names in column A on my total page, listed the tab names in K1:K12 ("Jan", "Feb", etc. - list them anywhere you want, but change the "K's" in the formula accordingly), and inserted this code into the column where I wanted the payroll summarized (in this case, column B):

=SUMPRODUCT(SUMIF(INDIRECT("'"&$K$1:$K$12&"'!A$1:A31"),$A1,INDIRECT("'"&$K$1:$K$12&"'!C$1:C31")))

This formula searches all sheets to find matches to the names in column A and totals the payroll from the range in column "B".  The data I wanted to total was contained within rows 1 through 31 of column B in this case - change those to accomodate your data.  Simply copy the formula down the column.

I wanted to summarize the OT in column B also so I changed the "A's" to "B's" (except for the search Criteria $A1) and the "C's" to "D's":

=SUMPRODUCT(SUMIF(INDIRECT("'"&$K$1:$K$12&"'!B$1:B31"),$A1,INDIRECT("'"&$K$1:$K$12&"'!D$1:D31")))

You can do the same for additional columns if you have Section 125, etc.
« Last Edit: October 12, 2011, 10:48:03 PM by beancounter » Logged

bc
Pages: 1   Go Up
  Add bookmark  |  Print  
 
Jump to:  

Related Topics
Subject Started by Replies Views Last post
Wage Segregation & 6204 with NPD Manual Rules & Legislation AuditorLisa 7 6827 Last post August 14, 2008, 11:34:58 AM
by auditor1
AuditBible Bulletin ~ 8810 & Supervisors AuditBible Tools auditor1 0 2666 Last post September 25, 2008, 11:10:39 PM
by auditor1
Schwarzenegger signs SB-1145 & AB-1874 to Reform SCIF Manual Rules & Legislation beancounter 0 2060 Last post September 29, 2008, 07:51:41 PM
by beancounter
AuditBible Bulletin ~ Metal Gauge & Stamping AuditBible Tools auditor1 0 2077 Last post April 07, 2009, 10:19:23 AM
by auditor1
AuditBible Bulletin ~ Calf Nurseries & Heifer Ranches AuditBible Tools auditor1 0 2295 Last post April 21, 2009, 11:01:02 AM
by auditor1
6th Annual TXWC Forum & Tradeshow Announcements auditor1 0 5246 Last post July 21, 2009, 06:23:57 PM
by auditor1
Microsoft Store
Unique Hits: 1851187
Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2015, Simple Machines
AuditBible © 2008 | Sitemap
Valid XHTML 1.0! Valid CSS!