Ok, that layout looks clear enough. I started by copying the below data into
A2:E12 of a blank spreadsheet; if your data are in different columns you'll
need to modify my formulae accordingly. No doubt there are other ways of
doing it, but here's the method that I came up with. By the way, this method
counts on there being no blank lines within a supervisor group, and at least
one blank line between each group.

We'll start by creating some helping columns:

J2: =IF($A2="","",IF($A1="","H","D"))
I2: =IF($J3="",ROW(),I3)
H2: =IF($J2="H",H3,IF($J1="H",ROW(),H1))
G2: ="R"&$H2&"C:R"&$I2&"C"

You can copy these four formulae all the way down your data. Col J
determines whether this is a blank line between groups (""), or a supervisor
row ("H"), or a subordinate row ("D"). Col I uses that information to list
in every cell the LAST row of the current group. Col H does the same but
lists the FIRST row of every group.

G uses cols H and I to construct a string that describes the range for this
group, but it does it in R1C1 format instead of the more usual A1 notation.
Personally I use R1C1 all the time, but if you're not used to it you can read
more about it in the Excel help, or ask here and I'll explain it. I did most
of this in A1 notation on the assumption that it's what you're used to, but I
couldn't think of a way to do this in A1 notation so I did it the way I'm
used to.

Now we just need to set up the COUNTIF statement for each supervisor. In B2
put =COUNTIF(INDIRECT($G2,0),"Sat")/($I2-$H2+1). This displays the fraction
of "Sat"s in this column for the range in G2; you can copy this to C2:E2 and
it'll show those percentages too. Then copy the same formula to each of the
other supervisor rows to get the same result for them.

If you're not used to the INDIRECT function, all it does is let you
construct a string in some cell that looks like an address or range, and then
get Excel to recognize it as such. Normally Excel assumes the address will
be in A1 notation, so if it's in R1C1 notation you have to add the extra
"FALSE" argument (which I wrote as simply "0").

I didn't actually explain much, here, just showed you the formulae and added
a few bare lines of description. I'm thinking once you put this in your
spreadsheet and look at it a bit, you'll figure out anything that isn't
immediately obvious. But if I rushed over anything too quickly, feel free to
ask more, or (better yet) email me - my address is in my profile.

--- "ColonelK0rn" wrote:
Bridges, Joe 100% 100% 100% 100%
Lynch, Brian Sat Sat Sat Sat

Bore, Whata 100% 0% 100% 100%
Phillips, Stu Sat Non-Sat Sat Sat

Cash, Johnny 50% 50% 75% 100%
Dress, Black Non-Sat Non-Sat Non-Sat Sat
Hair, Curly Sat Sat Sat Sat
Foot, Club Non-Sat Non-Sat Sat Sat
Potter, Harry Sat Sat Sat Sat

....My problem is trying to calculate values based off of non-numeric
values. I fiddled around with trying to set an array, since I can use
variables, but I couldn't quite get it figured out.

As it is, I have two data metrics: for the Pre-test, and then a final. I'd
ultimately like to calculate each supervisors' pre-test results, and then the
final test results. Of course, the Sat results are desired, so if the
pre-test is non-sat, and appealed to a Sat result, then that's what the
second metric is calculating.