You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Schannah" wrote in message
...
Thanks, that worked really well. I fixed the problem myself prior to
reading
these replies but I did it by naming each array with its corresponding
cocktail name (so SexontheBeach, in cell B1, is followed by the array
B2:B12
which has been named "SexontheBeach") and using my typed-in cocktail name
to
locate that by name, and counting this with COUNTA(INDIRECT()). When I put
the system into my main spreadsheet, instead of this test one, I will use
your method instead, as it's infinitely simpler than naming 70-80 arrays
separately.

"T. Valko" wrote:

Try something like this:

B1:D1 = drink name
A2:A10 = ingredient names (not really needed for the formula)
B2:D10 = measures
A13 = randomly chosen drink name

=COUNTA(INDEX(B2:D10,,MATCH(A13,B1:D1,0)))

--
Biff
Microsoft Excel MVP


"Schannah" wrote in message
...
I'm making a spreadsheet that will help me to test myself on cocktail
recipes. The spreadsheet so far contains a large table with a row of
cocktail
names at the top and a list of ingredients down the side. The cell
values
represent how many measures of a given ingredient are used in a given
cocktail. So, for example, a Sex on the Beach will have values of 1
entered
into the cells in the rows corresponding to peach schnapps and vodka,
and
values of 3 entered into the cells in the rows corresponding to orange
juice
and cranberry juice.

What I am now trying to do with this spreadsheet is write some formulae
that
will allow me to test my knowledge of the recipes (I am doing this
currently
in a much smaller test spreadsheet including only three cocktails). So
far
I
have used the following formula to generate a cocktail name selected
randomly
from the top row:

=INDEX(B1:D1,ROUNDUP(RAND()*COUNTA(B1:D1),0))

This comes up with one of three cocktail names, hopefully each equally
likely to be chosen.

Then, because the result of this formula is constantly changing, I copy
out
the first result and use that to refer to the recipe table so that my
spreadsheet can tell me whether I have the ingredients right or wrong.

For instance, I will write out "Sex on the Beach" in cell A19, and in
the
rows underneath it I will write out the ingredients that I believe to
be
included. To test what happens if I get it wrong, I deliberately omit
one
ingredient, put in too much of another, and put in an extra ingredient
that
shouldn't even be there.

So:

Sex on the Beach
Vodka
Tequila
Orange juice
Cranberry juice

In the adjacent cells I put the number of measures:

Sex on the Beach
Vodka | 2
Tequila | 1
Orange juice | 3
Cranberry juice | 3

In the cells to the right of the numbers, I put the following formula:

=IF(HLOOKUP($A$29,$A$1:$E$12,VLOOKUP(A30,$A$1:$E$1 2,5,FALSE),FALSE)=B30,"Right","Wrong")

This returns "Right" if my suggested number of measures for a given
ingredient is the same as that in the table, and "Wrong" if it does
not.
The
next column over contains the formula:

=IF(C300,HLOOKUP($A$29,$A$1:$E$12,VLOOKUP(A30,$A$ 1:$E$12,5,FALSE),FALSE),"")

This will show the real value whether my guess is correct or incorrect
and
#N/A if there is nothing written there at all. This may seem redundant
but
it
enables me to have an array that only shows the real measures of the
ingredients if I have made a guess at it already.

The problem is that this doesn't show me if I have omitted an
ingredient,
which of course in this case I have. In order to fix this I decided to
create
a formula in a different cell that would count the number of
ingredients I
have proposed that actually appear in the drink [simply
=COUNTIF(D30:D36,"<0")] and compare it to the number of cells in the
column
headed "Sex on the Beach" or whatever cocktail has been chosen that are
not
empty. This last bit is the bit I can't do. Simply, I need to write a
formula
that selects a certain column and counts the number of non-zero values
in
that column. This is easy enough when you define the column as an array
(e.g.
b2:b13) but very difficult when you try to create the definition of
this
array using only the generated cocktail name to locate your column.
This
is
necessary because the cocktail name is variable, and that is the whole
point.

[I have tried to hash up a solution by creating ID numbers for the
columns
at the bottom of the table, and using
=ADDRESS(2,HLOOKUP(A29,A1:E13,13,FALSE),1,1) and
=ADDRESS(13,HLOOKUP(A29,A1:E13,13,FALSE),1,1) where row 13 contains the
ID
numbers, and this returns "$B$2$" and "$B$13" which are the correct
limits
to
the array I want, but should I enter these two functions into a COUNTA
function, with any combination or lack of quotation marks, I get an
error
(except for when I place quotation marks before addressfunction1 and
after
addressfunction2, in which case I of course get 1.)]

So: does anybody know how I can get COUNTA to count the non-zero values
in
an array that isn't fixed?

Sorry for the length of the question, but I hope the context helped to
describe the problem better.