Hello everybody

[Emilio Arnés Vila]

I have these two issues with excel for mac 2001

--The first is a cosmetic problem with the visibility of cells. In most
templates that came with the program only the relevant range of cells is
visible (say a1 to h60) or operative and the rest of the sheet is light grey
and not selectable. How do I achieve this same view for my own worksheets???

--The second issue deals with counting a number of items in a list that
comply with two or more conditions, as in "count items in column a that are
so and so in col. B" is it a nested function or what???

Thanks everybody for your help

Emilio

 

[JE McGimpsey]

--The first is a cosmetic problem with the visibility of cells. In most
templates that came with the program only the relevant range of cells is
visible (say a1 to h60) or operative and the rest of the sheet is light grey
and not selectable. How do I achieve this same view for my own worksheets???

You can hide the unused rows and columns. If your template uses 10
columns and 50 rows, type

J:IV

in the Name box at the left of the Formula Bar. CTRL-click one of the
selected column headers and choose Hide (or choose Format/Columns/Hide
from the menu bar).

Similarly, type

51:65536

into the name box and hide the rows.

 

[JE McGimpsey]

--The second issue deals with counting a number of items in a list that
comply with two or more conditions, as in "count items in column a that are
so and so in col. B" is it a nested function or what???

One way:

Use the SUMPRODUCT() function. Say you wanted to count items in column A
that have a 1 in them and also have a 2 in the corresponding cell in
column B:

=SUMPRODUCT(--(A1:A100=1),--(B1:B100=2))

The double unary minus (-) first coerces the TRUE/FALSE values in the
arrays to -1s and 0s, respectively, since SUMPRODUCT() requires numeric
arrays. The second unary minus converts the -1 to +1. The arrays are
then multiplied and the 1's in the resulting array are summed.