The months are dates, the first of the month to be exact, formatted to show
Month and Year (Feb-07)

So, the lighting bolt of "dynamic range" woke me up, it's been sooooo long
since I had used one. Yes, that fixed both of the errors, and lets me remove
almost half the size of the template file. Great advice!

Now onto my "extra" feature I wish to add... a list of all comments from the
survey data at the bottom of the summary page. But that's another topic...

"T. Valko" wrote in message
...
If I try these great formula with collum referances (Sheet1!$A:$A) it
only returns #NUM.

You can't use entire columns as range references with Sumproduct (unless
you're using Excel 2007).

I just thought of something. If the highest rating is 5 and you want to
count 4's and 5's then all we need to do is get the count that is greater
than or equal to 4:

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

Now, about the #N/A........

Is "month1" really a DATE or is it a TEXT string as you have represented
it?

That's why it's best to tell us *exactly* what you have instead of making
stuff up!

As far as not knowing how big the range is, just use a larger range
reference that you know you will never exceed, or, use a dynamic range.
See this on how to create a dynamic named range:

http://contextures.com/xlNames01.html#Dynamic

Biff

"Brian H" wrote in message
news:XoZDh.1123526$5R2.149498@pd7urf3no...
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.