formula to create a single count covering multiple conditions

[shaneb]

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

[rwgrietveld]

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


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

see attchement

[DonkeyOte]

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

[shaneb]

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

[shaneb]

Thanks Ricardo for the quick reply,

the formula is a great alternative for the "Individual" Team Type

unfortunately it still counts every row for the "School" Team Type

fyi : further to the right in cells G20:H26 I placed a table with the expected results

Shane