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/ex...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/ex...CH010036991033
http://www.bettersolutions.com/excel...N620422111.htm
--
Steve
"Max" wrote in message
...
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" wrote:
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 1s and 2s,
a
few 3s and Fails.
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 1s I have in that column and then how many 2s,
etc.
Is it possible for me to put on the cover sheet that I have 105 1s, 52 2s
12 3s, etc? Let me rephrase that. Is there anyway to do this
automatically?
Ive tried, and please dont think less of me, to understand vlookup,
index,
match and other formula types but its mostly gibberish to me. Please
help.
It takes too dern much time to look up this info on over 300 people.
Again Thank You.