How to use vlookup in an array formula
 

In sheet1, I have rows and columns whose cells contain strings like "S", "M",
"L", and "XL". Some cells are empty. In sheet2, is a column A of the same
strings and a column B of corresponding values, e.g. 1 corresponds to "S", 4
corresponds to "M", etc. I want to be able to sum the rows and columns in
sheet1 based on the table in sheet2. The array in sheet1 is not the same size
as the value column in sheet2.

I tried to use an array formula like
{sum(vlookup(sheet1!a1:a20,sheet2!a1:b4,FALSE))} but it doesn't seem to sum
the array, only the first cell. Also, the empty cells seem to result in the
#value error.

Any suggestions on how to produce the result I'm looking for? Thanks in
advance.

How to use vlookup in an array formula
 

Fow about

=COUNTIF(Sheet1!A1:D20,A1)


"Jill" wrote in message
...
In sheet1, I have rows and columns whose cells contain strings like "S",
"M",
"L", and "XL". Some cells are empty. In sheet2, is a column A of the same
strings and a column B of corresponding values, e.g. 1 corresponds to "S",
4
corresponds to "M", etc. I want to be able to sum the rows and columns in
sheet1 based on the table in sheet2. The array in sheet1 is not the same
size
as the value column in sheet2.

I tried to use an array formula like
{sum(vlookup(sheet1!a1:a20,sheet2!a1:b4,FALSE))} but it doesn't seem to
sum
the array, only the first cell. Also, the empty cells seem to result in
the
#value error.

Any suggestions on how to produce the result I'm looking for? Thanks in
advance.

How to use vlookup in an array formula
 

Ok. I will use a better example. Let's say my sheet 1 shows car sales like
Honda, Ford, etc. The rows are months like Jan, Feb, etc. and the columns are
dates (1-30 or 31). Of course there are many empty cells because there are
dates that I did not sell any car. (To keep it simple, I sell only one car if
there is any sale on a day. If I sell Honda, I put "Honda" in my cell, and
Ford, "Ford," etc.) The sheet 2 shows the price of each car, and A column has
car brands and column B has prices of each car. Now I would like to see how
much I made at the end of each month, and would like to put each total on
the cell next to the last date of each month.

"Bob Phillips" wrote:

Fow about

=COUNTIF(Sheet1!A1:D20,A1)