Totaling Column Question

jbyrne · Apr 17th, 2002, 09:49 AM

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 · Apr 17th, 2002, 10:03 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 17th, 2002, 09:49 AM	#1
jbyrne Board Regular Join Date: Feb 2002 Posts: 165	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

Apr 17th, 2002, 10:03 AM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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 ]