Pulling #'s from multiple spreadsheets

  • Thread starter Thread starter MarkB
  • Start date Start date
M

MarkB

I have 10 seperate spreadsheets (single worksheet in each)
that have about 10 columns and 2000 rows each of data. One
of the column headers is STATE in each.

Does anyone know a way I can get a total number of people
that have the state PA (for example) through all the
spreadsheets?

THANKS!
 
Hi
try the following:
1. On your summary sheet enter the name of your 10 different tab names
in a separate range. e.g. in the range X1:X10
2. Enter the following formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'" & X1:X10 & "'!A1:A2000"),"PA"))

Assumption: column A contains the state information
 
Back
Top