WinXP Excel 2k3

Ok...this will be long so thank you in advance for reading:

I have a spreadsheet with survey results (Analysis!) about users' device
utilization (print/scan/fax/copy) imported all as text from web app.

Linked to headcount data for locn sizes based on location# in survey via
this formula in column range $I$7:$I$151...
=VLOOKUP(Analysis!G34&"*",Area!$E$5:$F$787,2,FALSE )
....and now want to derive some stats from the answers in a summary tab
(Matrix!).

Matrix!$B$5:$B$8 are arbitrary ranges which will change until a set range is
deemed optimal for building device profiles based on locn size and device
volume, and so formulas need to recalc each time ranges change.
HC Range
1-10
11-30
31-50
50-

All the other calcs on Matrix! are based on HC Range col, because they go
fetch the info in Analysis! based on whether or not that locn fits the range
size on this column. This is the dynamic range part. Bernie D. was kind
enough to help with counting the # of locns which fit the range on each cell;
I've then been able to modify his formula in Matrix!$G5:$G8...
=COUNTIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matr ix!$B5,FIND("-",Matrix!$B5)-1)))-COUNTIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix! $B5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))))
....to give me average # of devices per device type by replacing countif with
sumif function and dividing by the results of the countif formula...
=(SUMIF(Analysis!$I$7:$I$151,"="&VALUE(LEFT(Matri x!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$V$7:$V$151)-SUMIF(Analysis!$I$7:$I$151,""&VALUE(MID(Matrix!$B 5,FIND("-",Matrix!$B5)+1,LEN(Matrix!$B5))),Analysis!$V$7:$V $151))/Matrix!$G5

I thought I would be able to use this methodology throughout but...Now I'm
trying to figure out device volumes. Problem is, I've hit a wall trying to
use the min, max and avg within the range condition. The Analysis! tab needs
to also be free to be sorted and resorted for other purposes w/o affecting
the results of the Matrix!. So I want each formula to give me the Min, Max
and Avg of all records in Analysis!$7:$151 that fall headcountwise within the
range specified, AND I need each formula to ignore zero and blank cells.
Volumes are located as follows:
Print Volume: Analysis!$AH$7:$AH$151
Copy Volume: Analysis!$CQ$7:$CQAH$151
....etc, I can plug in the others once I have a working formula

I hope I gave enough info and didn't confuse things. I'll be more than happy
to share the file if I'm told where to send it. I've been working on it for 2
days now. I tried this syntax which in my feeble mind is what I want but
excel won't accept it, it shades [$I$151"="] as an error
=min(if(and(Analysis!$I$7:$I$151"="&VALUE(LEFT(Ma trix!$B5,FIND("-",Matrix!$B5)-1)),Analysis!$I$7:$I$151""&VALUE(MID(Matrix!$B5,F IND("-",Matrix!$B5)+1,LEN(Matrix!$B5)))),Analysis!$AH$7: $AH$151))

Hopefully, whatever I get from this post I can also use for MAX and AVERAGE
functions.

Thank you all who managed to read the whole thing before falling asleep. :-)
When you wake up, I would LUUUUUV some help!

--
Hile