Excel 2007 formula, multiple reference range
 

Help. I am collating data from 6 facilities. I have one worksheet with 7 pages. The first is a summary of all data. The other 6 have individual data for each facility. On the summary page, I need a total number, pulled from all 6 facilities. On the facility detail pages, I have summed the occurrences in that facility. Now, I need a formula that will let me get a grand total. For example:
pg 1 B5 needs to have the total from:
pg2 K5:K100 if J5:J100 = pg1A5
plus
pg3 K5:K100 if J5:J100 = pg1A5
plus
pg4 K5:K100 if J5:J100 = pg1A5
plus
pg5 SAA
plus
pg 6 SAA
plus
pg 7 SAA

The reason why I want it to reference the value from pg1 A5, instead of a specific, typed, name is that I have a long list of occurrences per name and I want to be able to copy the formula down the name list.
I have tried SUMIF, COUNTIF, SUM(IF...., COUNT(IF..., PRODUCT and multiple other combinations. I have tried them with a simple enter and a CSE. I'm lost. can anyone help? Thank you!



Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET: Jazz Up the Xml Control
http://www.eggheadcafe.com/tutorials...p-the-xml.aspx