Thank you very much for your response.

This might help if I have only one lab test (hemoglobin) to look at and
fixed number of patients. The number of patients (and thus sheets) changes
frequently. I also have multiple other lab results to look at (like calcium,
sodium,....).

I have used VBA in Microsoft Access , but never in Excel. I was wondering if
a code in Excel might help me (if I get help with Hemoglobin, I would use it
as a guidance for how to get the values I need for other labs, like
calcium,...). The code I need would give me the following:
1) Get the total number of patients I have (as count of all excel sheets I
have minus 1, since I have a summary sheet). Call this variable
"TotalPatientCount".

2) Get the number of patients with Hemoglobin value (in cell A9 of all the
sheets) between 10 and 12; call this "Count". I presume this would be done
via a loop, something like:
Count =0
For i = 2 To TotalPatientCount
With Worksheets(i)
If .Range("A9") = 10 and <=12 Then
Count = Count + 1
end if
end with
next i
end loop.

3)Percentage would be 100*count/TotalPatientCount

Any help in writing the above code would be very much appreciated.

"Gary''s Student" wrote:

I would definitely make a local table on the summary sheet. Say in Z1 we
enter:

=INDIRECT("Sheet" & ROW(A1) & "!A9") and copy down thru Z50

Then all we need is:

=SUMPRODUCT((Z1:Z50=11)*(Z1:Z50<=12))/50 and format as percentage.

--
Gary''s Student - gsnu200909


"wissam" wrote:

Hi,
I have data on patients in excel file, with each patient's data placed on a
separate sheet. For instance: PatientA has data on worksheet "PatientA"
(Sheet2) where cell A9 is hemoglobin data ... PatientZ has data on worksheet
"PatientZ" (Sheet50) where cell A9 is hemoglobin data. I have a summary
worksheet called "Summary" (Sheet1). I need cell A9 in the "Summary" (Sheet1)
sheet to give me the percentage of patients whose Hemoglobin value is between
10 and 12 (that is percentage of A9 values in Sheet1 to Sheet50 with value
between 10 and 12). Any help is appreciated. Thanks.