referencing to another sheet

S

Silly Patty

I am a trying to learn excel and cannot get how to do this.
I am working on a real estate sheet
On sheet Home summary I am to calculate the number of houses in the
NESector from the Home Data Sheet using the RANGE names.

Data Sheet Set up as below
NESector Price Size of lot

Home Summary Set up as below
NESector -Count the Number of lots referencing from the Home Data Sheet
NESector - Calculate the total value referencing from the Home Data
Sheet
I know I am supposed to put exclamation mark after the Home Data Sheet
but every time I try I seem to not get the layout of the formula right.
Any suggestions
 
S

SteveG

In the summary sheet I used NESector in A1, Value of NESector in B1 fo
my column headings. I am assuming that in the data sheet, there is
column for Sector, Price and Size of lot. I put these in A1:C1. In m
summary for the count of NESector homes I used:

=COUNTIF('Data Sheet'!A2:A6,"NESector")

for the value I used:

=SUMPRODUCT(('Data Sheet'!A2:A6="NESector")*('Data Sheet'!B2:B6))

You can also use your column headers in the summary page as reference
rather than typing in NESector in the formulas above so then,

=COUNTIF('Data Sheet'!A2:A6,A1)

and

=SUMPRODUCT(('Data Sheet'!A2:A6=A1)*('Data Sheet'!B2:B6))




Does that help?

Stev
 
Top