Summing different cells in column across multiple worksheets

[hovercraftdriver]

Hello all, new guy here and relatively Excel inexperienced.

I am working on a task analysis involving functions performed by different operators, each function marked by an "X" and totaled at the bottom of each operator column. I have seven sheets, named "Main Engine, Propulsion System, APU and ELEC.", etc. Columns B-F on each sheet are headed "C/M, Engr, Nav, LM, DE" respectively.

I need to add/total the bottom figure in each column (total of number of X's for each column) on a separate, final worksheet. Each of the worksheets contains a different number of functions, therefore the total of X's in each column is listed in a different cell# on each worksheet.

So this is my dilemma as the thread title states: I need to be able to sum/total the amount form different cell numbers (same column) across different worksheets into a cell in a separate, final worksheet.

I have figured out how to do this if the numbers are in the same cell across worksheets, but not varying cells by column. Any help would be greatly appreciated.

 

[stanleydgromjr]

hovercraftdriver,

Welcome to the MrExcel board.

Excel Workbook
	B	C	D	E	F
1	C/M	Engr	Nav	LM	DE
2	22
Summary

You will have to expand the formula in cell B2 to include all the sheetnames.:
=COUNTIF('Main Engine'!B:B,"X")+COUNTIF('Propulsion System'!B:B,"X")+COUNTIF(APU!B:B,"X")


Then do the same for columns C thru F.


Have a great day,
Stan

 

[stanleydgromjr]

hovercraftdriver,

If you are looking to total the "X"s for individual operators, then can we have a screenshot of one of the sheets, and a screenshoot of your Summary/Total sheet.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[hovercraftdriver]

Thanks for the replies. Column A is the list of tasks/functions and the length of Column A varies. "X" represents each time any operator performs this task and is totaled at the bottom. Sheet 1 may be cell B199, sheet 2 B458, sheet 3 B245. I need to take these totals to the summary sheet, which is just a concept and not formatted yet. I am just looking for a formula to get these totals to summary sheet B199+B458+B245 (or range)= summary total for each column and each sheet. Thanks

 

[stanleydgromjr]

hovercraftdriver,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[hovercraftdriver]

APU AND ELEC.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 416px"><COL style="WIDTH: 67px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 53px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>EQUIPMENT/ Casualty/ IMMEDIATE ACTION/ FOLLOW-UP ACTION</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center" colSpan=5>LCAC Crew Positions</TD></TR><TR style="HEIGHT: 90px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"></TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">C/M</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Engr</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Nav</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">LM</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">DE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold">2-5. APU AND ELECTRICAL</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold">Loss of APU</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-DECORATION: underline">IMMEDIATE ACTION Crewmember</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>1. Shut down affected APU.</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-DECORATION: underline">FOLLOW-UP ACTION</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>1. Depress affected generator key on UKB (APU</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>ELEC Menu) to OFF.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>2. Check APU fuel pressure. E</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>3. Check circuit breaker panels and reset. D</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-WEIGHT: bold">CAUTION</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>If an APU automatic shutdown is not accompanied with an abnormal</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>CAMS indication, restart of the APU will not be attempted until</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>the APU compartment has been inspected for and declared free of</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>residual fuel to avoid an APU compartment fire.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>4. Attempt restart IAW standard procedures. E</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD>5. If start fails, place START/OFF switch to OFF. Do</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD>not reset T/R PWR/B switch.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD>6. Manually establish ring bus configuration by</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD>pushing in 1S BT FDR, 2S BT FDR, LC1 BT FDR,</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD>LC2 BT FDR, and 2S LC3 FDR breakers located</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD>on AC Distribution Panel.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD>7. When operational conditions permit, check APU</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD>BITE box.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Here is a portion of the first worksheet. The file was too large to insert the whole thing, according to Jeanie. Anyway, each column has an "X" where an operator has a responsibility. At the bottom of each column is a total for amount of X's in each column. For instance, B200(for C/M), the total is 3, C200(for Engr), the total is 48.

I would like a formula to total these numbers on the final sheet. I haven't developed the sheet, so formatting is wide-open as far as I am concerned. It would be nice if I could have on left column the sheets inependent totals, and across top the operator positions again, to break it down. I'll make an example real quick.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 223px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #00ffff">OPERATOR POSITIONS</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #00ffff; TEXT-ALIGN: center">C/M</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #00ffff; TEXT-ALIGN: center">Engr</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #00ffff; TEXT-ALIGN: center">Nav</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #00ffff; TEXT-ALIGN: center">LM</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #00ffff; TEXT-ALIGN: center">DE</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00">Casualty Categories</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BACKGROUND-COLOR: #ffff00">Main Engine</TD><TD style="TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: center">102</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BACKGROUND-COLOR: #ffff00">Propulsion System</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">49</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BACKGROUND-COLOR: #ffff00">APU and ELEC.</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">48</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BACKGROUND-COLOR: #ffff00">Comm.AND NAV.</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold">TOTAL</TD><TD style="TEXT-ALIGN: center">43</TD><TD style="TEXT-ALIGN: center">199</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Keep in mind, I manually inserted and totaled those numbers. Which I can do, but several people are going to take a cut on this and I want the numbers to change automatically if changes are made. If the only way to do the formula is to add the X's on the total sheet only, then so be it. But I would prefer to use the totals in the various cells on each sheet, and please remember, these cell numbers vary, because the length of the lists on the left is different on each sheet.

I hope this info helps you help me. Let me know if you need something else. Sorry, didn't get the requested info to you yesterday, but something else came up here at work. This is an awesome site. Thanks in advance for any assistance.

Edit** The Preview post tables look fine, but the actual thread tables look funky. Let me know if this is a problem and I'll redo.

 

[hovercraftdriver]

Also Stan, I tried the formula in your first post to simply gain X total (for column B in this instance) and it brings up a "save to" file window. After I manage to close this window, it gives me a #VALUE! error. Can you tell me why this happens?

 

[hovercraftdriver]

bump

 

[Dave Patton]

I do not know how your sheets are laid out or your full requirements.

Two quick thoughts follow.

A count of the "x" on a particular sheet, consider


=COUNTIF('APU and ELEC'!C3:C100,"x")


To secure the last number in a Column, try something like

=LOOKUP(9.99999999999999E+307,'APU and ELEC'!C:C)

 

[hovercraftdriver]

Dave, thanks for reply. Apparently Jeanie won't let me copy the whole sheet...too many cells. Is there no attachment function on this forum to post screenshots directly from PC?

Your formula works for some cells but not for others. I get a #VALUE error. Must be something (formatting) in the originating worksheet(s) I have screwed up. Any ideas why this happens?

I simply want to take data from various cells on various worksheets and input them to a "Totals" worksheet, then add up those values.