Using COUNTA for a variable array

[Schannah]

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(B11,ROUNDUP(RAND()*COUNTA(B11),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$12,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(C30>0,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(D3036,"<>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.

 

[John C]

Here is the way I did it, I think a slightly different way than yours.
Assumptions: You have a tab that has drinks listed across row 1 (starting in
B1), and so on, and ingredients down (starting in A2, listing all available
ingredients for all drinks). For my example, I defined Row 1 on the Drink tab
as DrinkList. I identified the cell that you will be trying to lookup the
number of ingredients as TheDrink.

Go to Insert-->Name-->Define, the name I used is IngNo (for Ingredient
Number)
=OFFSET(INDIRECT("$"&IF(MATCH(TheDrink,DrinkList,0)<26+1,CHAR(MATCH(TheDrink,DrinkList,0)+64),CHAR(INT(MATCH(TheDrink,DrinkList,0)-1)/26+64)&CHAR(MOD(MATCH(TheDrink,DrinkList,0)-1,26)+65))&"$2"),0,0,TotalIngredients,1)

The only other undefined value is TotalIngredients, which would be the total
possible ingredients (assuming nothing in cell A1 on Drink tab, could just
use COUNTA(Drink!A:A) instead.

Again, assuming your table is set up as follows

..........Drink A......Drink B.......Drink C......Drink D.....Drink E........
Ing
1.....1..............................1...............1................1.........
Ing
2.....................1..............1.................................1.........
Ing
3......1.............1............................................................
Ing
4.....................................1................1..........................

and so forth.


--
John C

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(B11,ROUNDUP(RAND()*COUNTA(B11),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$12,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(C30>0,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(D3036,"<>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.

 

[T. Valko]

Try something like this:

B11 = drink name
A2:A10 = ingredient names (not really needed for the formula)
B210 = measures
A13 = randomly chosen drink name

=COUNTA(INDEX(B210,,MATCH(A13,B11,0)))

--
Biff
Microsoft Excel MVP

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(B11,ROUNDUP(RAND()*COUNTA(B11),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$12,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(C30>0,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(D3036,"<>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.

 

[Schannah]

Although your understanding of the structure of my sheet is dead on, I think
there must be some difference in the way our sheets are set up, because I
pasted in this formula, replacing TheDrink with reference A19, which was the
cell containing my typed drink name to look up, DrinkList with B11, which
was my list of cocktail names, and TotalIngredients with 11 (I also tried
COUNTA(A2:A12), which was the number of total ingredients present in the
table. The formula returns a #VALUE error, and because I don't understand the
process that the formula is going through, I'm not sure how to modify it for
my sheet.

The problem is currently fixed; I fixed it myself by defining the column
underneath each cocktail name in the test spreadsheet with the name of the
cocktail (so, under "SexontheBeach" in cell B1, array B2:B12 has been named
SexontheBeach) and using my typed-in drink name to locate that array, and
counting it with COUNTA(INDIRECT()). As this will get a little tiresome when
I have 70-80 different cocktails in the spreadsheet, I also tested out the
next reply given on this thread, which was
=COUNTA(INDEX(B212,,MATCH(A19,B11,0))) (that's with my values put in),
and that works fine too. Still, it'd be good to find out how to make yours
work, and what process it uses, in case of possible bugs in my first two
methods.

Here is the way I did it, I think a slightly different way than yours.
Assumptions: You have a tab that has drinks listed across row 1 (starting in
B1), and so on, and ingredients down (starting in A2, listing all available
ingredients for all drinks). For my example, I defined Row 1 on the Drink tab
as DrinkList. I identified the cell that you will be trying to lookup the
number of ingredients as TheDrink.

Go to Insert-->Name-->Define, the name I used is IngNo (for Ingredient
Number):
=OFFSET(INDIRECT("$"&IF(MATCH(TheDrink,DrinkList,0)<26+1,CHAR(MATCH(TheDrink,DrinkList,0)+64),CHAR(INT(MATCH(TheDrink,DrinkList,0)-1)/26+64)&CHAR(MOD(MATCH(TheDrink,DrinkList,0)-1,26)+65))&"$2"),0,0,TotalIngredients,1)

The only other undefined value is TotalIngredients, which would be the total
possible ingredients (assuming nothing in cell A1 on Drink tab, could just
use COUNTA(Drink!A:A) instead.

Again, assuming your table is set up as follows

.........Drink A......Drink B.......Drink C......Drink D.....Drink E........
Ing
1.....1..............................1...............1................1.........
Ing
2.....................1..............1.................................1.........
Ing
3......1.............1............................................................
Ing
4.....................................1................1..........................

and so forth.


--
John C

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(B11,ROUNDUP(RAND()*COUNTA(B11),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$12,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(C30>0,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(D3036,"<>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.

 

[Schannah]

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.

Try something like this:

B11 = drink name
A2:A10 = ingredient names (not really needed for the formula)
B210 = measures
A13 = randomly chosen drink name

=COUNTA(INDEX(B210,,MATCH(A13,B11,0)))

--
Biff
Microsoft Excel MVP

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(B11,ROUNDUP(RAND()*COUNTA(B11),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$12,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(C30>0,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(D3036,"<>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.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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.

Try something like this:

B11 = drink name
A2:A10 = ingredient names (not really needed for the formula)
B210 = measures
A13 = randomly chosen drink name

=COUNTA(INDEX(B210,,MATCH(A13,B11,0)))

--
Biff
Microsoft Excel MVP

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(B11,ROUNDUP(RAND()*COUNTA(B11),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$12,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(C30>0,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(D3036,"<>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.