I don't even know what to ask

[greenusmarine53]

For all you Excel guru's, not only do I need an answer, I need to know how to
ask the question. Please bear with me and thank you for your time.

I have a spreadsheet with multiple tabs (worksheets?) and I am using the
first one as a cover sheet type doohickey. The following pages are set up to
track who has done what training by section, then all that comes together on
the cover sheet. So imagine if you will about a dozen tabs named for each
work section, with a list of names that work in that section and then about
thirty columns labeled with the training requirement and whether the person
has done it or not. Eezy peezy.

It gets complicated when 'they' (the powers that be) want to know the grade
the person passed (or failed) by. So next to the column labeled PFT (or
Physical Fitness Test) for example is a column asking what class (or grade)
the person got; 1st class, 2nd, 3rd, Failed and partial. So in that column
there are multiple 'values' I guess they're called. A bunch of 1’s and 2’s, a
few 3’s and Fail’s.

Now, on the cover sheet, I have the percentage of how many people ran a PFT,
in this case %56.67 of the people have ran a PFT. What I need now is the
number of people who got a 1st class, 2nd class and so on. Not a percentage,
but an actual number. The old way of doing this would have me go through each
tab and count how many 1’s I have in that column and then how many 2’s, etc.

Is it possible for me to put on the cover sheet that I have 105 1’s, 52 2’s
12 3’s, etc? Let me rephrase that. Is there anyway to do this automatically?
I’ve tried, and please don’t think less of me, to understand vlookup, index,
match and other formula types but it’s mostly gibberish to me. Please help.
It takes too dern much time to look up this info on over 300 people.

Again Thank You.

 

[Max]

My thoughts/offering to you ..
Take a look-through these 3 selected samples from my archives,
which imo, is of relevance. Samples are in rough order of complexity.

1. Interactive Summary.xls
http://savefile.com/files/414328

Interactive Summary: A simple formulas driven model
which extracts figs by attribute (selectable via a droplist)
from various identically structured source data shts
into an easy-to-view summary sheet

2. Exec Summary.xls
http://savefile.com/files/1925536

Executive Summary: A formulas driven model which
extracts Open status cases for droplist
selectable Dept and Year. Source sheets are
named by year, eg: 2008

3. Military Leave Tracking Model.xls
http://savefile.com/files/1953053

Military Leave Tracking Model: Formulas driven
model with facilitated leave application via
droplists, auto-leave calendar generation and an
auto-summary to output daily mission capability
readiness based on minimum manpower required for
scheduled missions

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

 

[AltaEgo]

If you find Max's examples too complex, you have the option of performing
your counts in each worksheet using the Countif() function and summing the
result of this through to your summary sheet using a 3D sum formula. So,
what is a 3D formula, you ask:

You will be used to summing In a worksheet - type '=Sum( {select your cell
range})', press [Enter] and the answer magically appears. A 3d formula does
the same type of thing but extends the formula across multiple worksheets.

Unfortunately, the Countif() function will not perform 3D calculations.

Breaking the problem into its parts:

A) Countif () to find the number of 1's, 2's, 3's etc.

Locate a vacant area in your first data worksheet, that you know is also
vacant in all other data worksheets. Create Countif() formula to add up your
1's, 2's, 3's, 4's etc.

Each formula will look something like:

=COUNTIF(B:B,1)

In the above:
- B:B is the range of cells in which to count
- 1 is the value to count in the range

If the value to be matched is not a number, you need to write your formula
slightly different so Excel knows it is dealing with a string:
=COUNTIF(B:B,"=1st")

Or, if you store the value in another cell:

=COUNTIF(B:B,K1) where K1 is the relevant cell

In place of B:B, substitute your own range. For example C2:C100 will
restrict the count range to that range of cells.

Microsoft's explanation of the function:

http://office.microsoft.com/en-us/excel/HP052090291033.aspx?pid=CH062528311033

Once you understand what the formula is doing, think of a range of cells
that are blank in each of your raw data sheets where you can put your
formula.

Create your formula in one sheet only.

Highlight your formula and copy TO THE SAME LOCATION in each summary
worksheet.

B) Now for the 3D sum:

-In your summary sheet, click the cell where you want your first result.

-In the formula bar, type "=Sum(" (without the quotes)

-Click the tab or the first sheet in the range of sheets that contain the
information, then click the relevant cell.

- Holding the [Shift] key, click the tab of the last sheet in the range of
sheets that contain the information.

- Type ")" to complete the formula then press [Enter]

Next, assuming you have you in data adjacent cells in all worksheets, copy
and paste your new formula across or down the same number of cells.


If my explanation causes confusion these references may help:

http://office.microsoft.com/en-us/excel/HP052019651033.aspx?pid=CH010036991033

http://www.bettersolutions.com/excel/EED883/QN620422111.htm

 

[AltaEgo]

It is critical to have your sheets for a 3D formula arranged together with
no stray sheets in the middle. For example, if your sheets were arranged:

Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6

you should not place a summary sheet or another sheet relating to something
else in the middle.

Oh! I omitted an example of a 3D sum:

=SUM(Sheet2:Sheet6!A1)


The above is saying sum the values in cell A1 in the range of sheets Sheet2
to Sheet6, inclusive. For a workbook with worksheets intact and in order
this will sum the values in cell A1 in sheets Sheet2, Sheet3, Sheet4,
Sheet5, Sheet6. If worksheets are not in order, it will sum only the sheets
between Sheet2 to Sheet6, inclusive. So, for example, if you move Sheet4
outside the range, Excel will adjust Sheet4 outside its calculation. If you
move a new sheet within the range of sheets, Excel will adjust, adding
relevant values in the new sheet.

--
Steve

If you find Max's examples too complex, you have the option of performing
your counts in each worksheet using the Countif() function and summing the
result of this through to your summary sheet using a 3D sum formula. So,
what is a 3D formula, you ask:

You will be used to summing In a worksheet - type '=Sum( {select your cell
range})', press [Enter] and the answer magically appears. A 3d formula
does the same type of thing but extends the formula across multiple
worksheets.

Unfortunately, the Countif() function will not perform 3D calculations.

Breaking the problem into its parts:

A) Countif () to find the number of 1's, 2's, 3's etc.

Locate a vacant area in your first data worksheet, that you know is also
vacant in all other data worksheets. Create Countif() formula to add up
your 1's, 2's, 3's, 4's etc.

Each formula will look something like:

=COUNTIF(B:B,1)

In the above:
- B:B is the range of cells in which to count
- 1 is the value to count in the range

If the value to be matched is not a number, you need to write your formula
slightly different so Excel knows it is dealing with a string:
=COUNTIF(B:B,"=1st")

Or, if you store the value in another cell:

=COUNTIF(B:B,K1) where K1 is the relevant cell

In place of B:B, substitute your own range. For example C2:C100 will
restrict the count range to that range of cells.

Microsoft's explanation of the function:

http://office.microsoft.com/en-us/excel/HP052090291033.aspx?pid=CH062528311033

Once you understand what the formula is doing, think of a range of cells
that are blank in each of your raw data sheets where you can put your
formula.

Create your formula in one sheet only.

Highlight your formula and copy TO THE SAME LOCATION in each summary
worksheet.

B) Now for the 3D sum:

-In your summary sheet, click the cell where you want your first result.

-In the formula bar, type "=Sum(" (without the quotes)

-Click the tab or the first sheet in the range of sheets that contain the
information, then click the relevant cell.

- Holding the [Shift] key, click the tab of the last sheet in the range of
sheets that contain the information.

- Type ")" to complete the formula then press [Enter]

Next, assuming you have you in data adjacent cells in all worksheets,
copy and paste your new formula across or down the same number of cells.


If my explanation causes confusion these references may help:

http://office.microsoft.com/en-us/excel/HP052019651033.aspx?pid=CH010036991033

http://www.bettersolutions.com/excel/EED883/QN620422111.htm



--
Steve

My thoughts/offering to you ..
Take a look-through these 3 selected samples from my archives,
which imo, is of relevance. Samples are in rough order of complexity.

1. Interactive Summary.xls
http://savefile.com/files/414328

Interactive Summary: A simple formulas driven model
which extracts figs by attribute (selectable via a droplist)
from various identically structured source data shts
into an easy-to-view summary sheet

2. Exec Summary.xls
http://savefile.com/files/1925536

Executive Summary: A formulas driven model which
extracts Open status cases for droplist
selectable Dept and Year. Source sheets are
named by year, eg: 2008

3. Military Leave Tracking Model.xls
http://savefile.com/files/1953053

Military Leave Tracking Model: Formulas driven
model with facilitated leave application via
droplists, auto-leave calendar generation and an
auto-summary to output daily mission capability
readiness based on minimum manpower required for
scheduled missions

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[greenusmarine53]

That did it! Thank you gentlemen. So very much.