This does work great in the test sample, but my real life sample has an
unknown number of surveys so the grid's depth is not known. If I try these
great formula with collum referances (Sheet1!$A:$A) it only returns #NUM. I
get an #N/A if I use a referance that would cover the maximum known surveys
(Sheet1!$A1:$A5000) as there is no data in the current cells.

My thanks to both you and Mama for a new twist to add to my next project...
:-)


"T. Valko" wrote in message
...
So the Top Two for month1 would be a count of 6 and for month 2 would be
14

Can you explain how you arrive at those figures.

For month1 there are a total of 12 entries that are either 4 or 5
For month2 there are a total of 14 entries that are either 4 or 5

Are you wanting to count the 4's and 5's for each month?

Sheet2 A1 = month1
Sheet2 B1 = month2

Enter this formula in Sheet2 cell A2:

=SUMPRODUCT((Sheet1!$A1:$A5=A1)*((Sheet1!$B1:$K5=4 )+(Sheet1!$B1:$K5=5)))

Copy across to B2

Biff

"Brian H" wrote in message
news:f2NDh.1128557$R63.792734@pd7urf1no...
I have been working on a summary page for reviewing some internal surveys
we do and am hopping soneone might have a simpler solution then what I am
having to do.

The data is in a table that has the month the survey was done and the
answers to the questions next to it. We have a 1 to 5 answer system, with
5 being best. It was not to hard to get the average score for each
question in a month, but finding a "Top Two" box total for each month was
a bit more chalanging.

Top Two Box, if you don't know, is where you take and count up only the
answers that got a 4 or a 5 for each month and then place that over a
total questions asked for the month to get a %.

I have 13 questions and for my solution I took the time to create a
sperate hidden sheet that has several DCOUNT and DCOUNT criteria tables.
I had to make a query for each question in each month, then total that...
it works, but I have to guess I missed a way to do this much easier. Now
I know that if I could have a count on each line, it would be much
easier, but in this case it is not an option. The users will be inputting
data into the table row by row, and in time deleting the older rows.

So, a brief example data file: (starting in A1)
month1 1 2 3 4 5 1 2 3 4 5
month1 1 2 3 4 5 1 2 3 4 5
month1 1 2 3 4 5 1 2 3 4 5
month2 1 2 3 4 5 1 2 3 4 5
month2 5 4 5 4 5 4 5 4 5 4

So the Top Two for month1 would be a count of 6 and for month 2 would be
14

The summary page has a collum for each month with the vaule of the month
(month1, month2) at the top.