Pulling #'s from multiple spreadsheets

[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!

 

[Frank Kabel]

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

 

[Mark B]

Jeez, what a freaking phenomenal program excel really is!

THANKS!