Totaling Column Question

[jbyrne]

I have employee initials in Col B
In column D I have text consisting of either TR or TO.
In Column E, I have text: containing YES or No.

I want on a seperate sheet to total the number of TO'S and TR'S for each employee.
Also give a percentage of the number of TO YES'S and the percentage of TR YES'S.

Any help would be appreciated

 

[Dave Patton]

count of number 2 criteria

=SUMPRODUCT((B2:B6="DGP")*(D2:D6="TO"))

count the number 3 criteria

=SUMPRODUCT((B2:B6="DGP")*(D2:D6="TO")*(E2:E6="Yes"))

If the data is on a sheet named 9
the formula on another sheet would be

=SUMPRODUCT(('9'!B2:B6="DGP")*('9'!D2:D6="TO")*('9'!E2:E6="Yes"))


Notes:
1. you can use cell references instead of hardcoding the criteria
2. you can determine and calculate the relevant denominator for the % calculation
3. extend the ranges as necessary
This message was edited by Dave Patton on 2002-04-17 09:08