Thread: 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

Could use the multiplicatons, another wat is use a double --


=SUMPRODUCT(--($D$2:$D$12=$A23),--($C$2:$C$12="School"))

see attchement

The formula you would want for D23 using SUMPRODUCT would be:

=SUMPRODUCT(--($D$2:$D$12=$A23),--($C$2:$C$12=D$21),--(MATCH($A$2:$A$12&"@"&$C$2:$C$12,$A$2:$A$12&"@"&$C$2:$C$12,0)=(ROW($A$2:$A$12)-ROW($A$2)+1)))
copied down

Wow
Thanks DonkeyOte

It works

Pretty clever formula. Way beyond my comprehension.

and I was able to extend the formula you posted to work out the Successul "School" Team Types in a District - eg

=SUMPRODUCT(--($D$2:$D$12=$A23),--($C$2:$C$12=D$21),--($E$2:$E$12="Successful"),--(MATCH($A$2:$A$12&"@"&$C$2:$C$12,$A$2:$A$12&"@"&$C$2:$C$12,0)=(ROW($A$2:$A$12)-ROW($A$2)+1)))

Thanks again

Shane