formula to create a single count covering multiple conditions

Hi all,

I am trying to find a formula which looks at several columns (Application ID, District, Team Type and Status) to help me populate a table.

Here is the sample data (also in the attached spreadsheet)


App ID Student Team Type District Status
1 James School FGR Successful
1 Mary School FGR Successful
2 Margaret Individual SDR Successful
3 Tom School NDY Unsuccessful
3 Peter School NDY Unsuccessful
4 Meagan Individual FGR Unsuccessful
5 Michelle School NDY Unsuccessful
5 Fiona School NDY Unsuccessful
6 Mark School NDY Successful
6 Ryan School NDY Successful
6 Vince School NDY Successful


I am hoping the table will show for each District the breakdown of each Team Type and how many had a status of "Successful"

I discovered a formula (which I don't fully understand) in another thread which I modified for the Team Type "Individual" that appears to work (not sure if there is an easier method):

SUMPRODUCT(($D$2:$D$12="FGR")*($C$2:$C$12="Individual"))

but modifying the formula for "School" Team type eg

SUMPRODUCT(($D$2:$D$12="NDY")*($C$2:$C$12="School"))

doesn't work because it counts the individuals in the team (ie each row) rather than counting the team once.

Any ideas to replace the ? in the table (extract below) with a formula would help keep me sane :-)


District Total Successful
FGR ? ?
SDR ? ?
NDY ? ?
Total 0 0


I am need a formula that will work in Excel 2003 and 2007

Thanks for taking the time to read this and hopefully you can help or point me in the right direction.

Shane