Summing different cells in column across multiple worksheets

hovercraftdriver · Mar 9, 2009

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.

Dave Patton · Mar 10, 2009

It will be useful if you provide:
- examples of your formulas
- clear explanation of what you are calculating

You could prepare a very small example that illustrates what you are doing.
You can then indicate your expected results and what you have tried.

hovercraftdriver · Mar 10, 2009

MainEngine

<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: 427px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><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><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 88px"><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><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Pilot</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Copilot</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><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">MAIN ENGINE</TD><TD></TD><TD></TD><TD></TD><TD></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">4</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></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 Main Engine</TD><TD></TD><TD></TD><TD></TD><TD></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">6</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></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 </TD><TD></TD><TD></TD><TD></TD><TD></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">8</TD><TD>1. Reduce propeller pitch and N2 on</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">X</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>affected side and increase propeller</TD><TD></TD><TD></TD><TD></TD><TD></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>pitch and N2 on affected side, if</TD><TD></TD><TD></TD><TD></TD><TD></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">11</TD><TD>required.</TD><TD></TD><TD></TD><TD></TD><TD></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">12</TD><TD>NOTE</TD><TD></TD><TD></TD><TD></TD><TD></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">13</TD><TD>Engine restart shall not be attempted following a PT rotation failure,</TD><TD></TD><TD></TD><TD></TD><TD></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">14</TD><TD>sudden stoppage, compartment fire or suspected stall.</TD><TD></TD><TD></TD><TD></TD><TD></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">15</TD><TD>NOTE</TD><TD></TD><TD></TD><TD></TD><TD></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">16</TD><TD>The Ready to Start Light will illuminate when engine N1< 6%. The</TD><TD></TD><TD></TD><TD></TD><TD></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">17</TD><TD>engine can be purged when the Ready to Start light is illuminated.</TD><TD></TD><TD></TD><TD></TD><TD></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">18</TD><TD>However, engine start is only possible when the Ready to Start light</TD><TD></TD><TD></TD><TD></TD><TD></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">19</TD><TD>is illuminated and all FADEC shutdown faults are cleared</TD><TD></TD><TD></TD><TD></TD><TD></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">20</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></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">21</TD><TD style="TEXT-DECORATION: underline">FOLLOW-UP ACTION</TD><TD></TD><TD></TD><TD></TD><TD></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">22</TD><TD>1. Set N1 knob to MIN for affected engine. </TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></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">23</TD><TD>2. Reset FADEC by depressing M/E RESET key</TD><TD></TD><TD></TD><TD></TD><TD></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">24</TD><TD>on the MAIN ENGINE Menu and purge for</TD><TD></TD><TD></TD><TD></TD><TD></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">25</TD><TD>high power shutdown, if required. FADEC will</TD><TD></TD><TD></TD><TD></TD><TD></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">26</TD><TD>inhibit start if EGT is > 400°F.</TD><TD></TD><TD></TD><TD></TD><TD></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">27</TD><TD>3. Attempt restart. </TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></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">28</TD><TD>4. If start normal, slowly increase N 1. EnsureN2</TD><TD></TD><TD></TD><TD></TD><TD></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">29</TD><TD>synchronizes with drive train N 2. Return to</TD><TD></TD><TD></TD><TD></TD><TD></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">30</TD><TD>design N1, N 2 , and prop pitch settings.</TD><TD></TD><TD></TD><TD></TD><TD></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">31</TD><TD>5. If attempted start fails, position M/E STOP key</TD><TD></TD><TD style="TEXT-ALIGN: center">X</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">X</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

Total

<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"><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><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</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><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #00ffff; TEXT-ALIGN: center">Pilot</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #00ffff; TEXT-ALIGN: center">Copilot</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><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><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><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">Main Engine</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">28</TD><TD></TD><TD></TD><TD></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">5</TD><TD style="BACKGROUND-COLOR: #ffff00">Propulsion System</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">12</TD><TD></TD><TD></TD><TD></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">6</TD><TD style="BACKGROUND-COLOR: #ffff00">APU and ELEC.</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: center">3</TD><TD></TD><TD></TD><TD></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">7</TD><TD style="BACKGROUND-COLOR: #ffff00">Comm.AND NAV.</TD><TD></TD><TD></TD><TD></TD><TD></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">8</TD><TD style="BACKGROUND-COLOR: #ffff00">Fire</TD><TD></TD><TD></TD><TD></TD><TD></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">9</TD><TD style="FONT-WEIGHT: bold">TOTAL</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B4</TD><TD>=COUNTIF(MainEngine!B3:B324,"x")</TD></TR><TR><TD>B5</TD><TD>=COUNTIF('Propulsion System'!B3:B249,"x")</TD></TR><TR><TD>B6</TD><TD>=COUNTIF('APU AND ELEC'!B3:B200,"x")</TD></TR></TBODY></TABLE></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

**EDIT- I don't know why these tables don't look right in the finished post. They look fine in the preview post. *shrugs*

In the top sheet(which is representative of 5 similar sheets), every column has a COUNTIF total of X's (different cell number, depending on sheet because of list length). I want to transfer these totals to the bottom table as shown. Obviously I can do that, but I was hoping there was an easier way to do this, then total each column on the bottom table, with individual formulas for each cell.

hovercraftdriver · Mar 10, 2009

I meant to say to avoid individual formulas for each cell

stanleydgromjr · Mar 10, 2009

hovercraftdriver,

See my Private Message to you (top right hand corner of MrExcel, Welcome, hovercraftdriver., "Your Notifications:".

Have a great day,
Stan

Summing different cells in column across multiple worksheets

hovercraftdriver

New Member

Dave Patton

Well-known Member

Excel Facts

hovercraftdriver

New Member

hovercraftdriver

New Member

stanleydgromjr

Banned

stanleydgromjr

Banned

stanleydgromjr

Banned

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock