Cell referencing in different worksheets

[j2dizzo]

Hi all,

how can I reference to cells in different sheets in a workbook. Assuming a user enters a new data in worksheet 1, then the sum is computed and displayed in worksheet 2 and 3.

Any help will be appreciated. Thanks in advance

j2dizzo

[olasa]

Two ways:

Either you just write the sheet name, a ! an the cell reference.
Example: =Sheet2!I5

Or you just type a = then go to sheet and click on the cell you want to reference. Excel will build the same formula, automatically.

Ola

[j2dizzo]

Thanks Olasa. I now get it

[j2dizzo]

Does someone maybe know why this formula is not working

=COUNTIF(AND('sheet1'!B:B;"Newspaper",'sheet1'!F:F;"Male"))

Thanx

[olasa]

Here are two solutions that work:

=SUMPRODUCT((Sheet1!B:B="Newspaper")*(Sheet1!F:F="Male"))
=SUMPRODUCT(--(B:B&F:F="NewspaperMale"))

Ola Sandström


Note:
-- will convert all TRUE to 1 and all FALSE to 0
Your suggestion will not work due to:
1) AND does not work with Arrays
2) and if AND had worked, all your ; should be =