Array - conditionally sum the 1st occurence in non-contiguous rang

[Christi]

I'm trying to summarize the number of people who attended any class, by
supervisor. Data for 3,000 employees is on one sheet and the summary on
another. Other people are entering the source data, and have consistently
found ways to break my hidden columns that had formulas to check columns CFI
and DGJ and EHK -- so the easiest obvious solution isn't workable. I've
tried to create an array formula on my summary sheet, but it's not returning
the expected results, so I'm off track. I have array formulas to count
people by class by supervisor working, but counting people across all the
classes by supervisor is not working.

I need an array formula that will give me the results at the bottom with
data formatted like this:

Source Data:
A B C D E F G
H I J K
1 Class 1 Class 2
Class 3
------------------
------------------ ------------------
2 Supervisor Emp plan reg attend plan reg attend
plan reg attend
3 Supervisor 1 Emp1 Y Y
4 Supervisor 1 Emp2 Y Y Y Y Y
Y
5 Supervisor 1 Emp3 Y
6
7 Supervisor 2 Emp4 Y Y Y
Y Y
8 Supervisor 2 Emp5 Y Y Y
Y

Result I’m looking for:
Total People (NOT total classes)
Supervisor 1 Supervisor 2
Planned 2 2
Registered 1 2
Attended 2 1

Thanks much

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Consolidate PT, Reverse PT.
With macro.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/01_09_10.xlsm