Unsure of Formula to Use

[rbeccaas]

Hi all!

I am trying to figure out the best formula to get a monthly result (in %) of "OK"s per person listed on my spreadsheet.:


Date Name RT1 RT2 RT3

1/1/2013 John c ok ok

1/3/2013 John ok b ok

1/3/2013 Mary ok ok ok

1/4/2013 Mary d ok e

1/4/2013 Sue a d ok

2/6/2013 John ok ok ok

2/7/2013 Sue d ok e


Desired outcome: John- Jan = 67% OK

John- Feb = 100% OK


Thought I had a decent COUNTIFS formula working, but it was fairly intense. It no longer calculates properly, so am looking to start over & hoping there's a better way.


Any advice is appreciated. Thanks in advance!

Regards,

Rebecca

 

[Hui]

Rebecca


Assuming the table above is in A1:E8

try:

=SUMPRODUCT(($A$2:$A$8>=(EOMONTH($G$4,-1)+1))*($A$2:$A$8<=EOMONTH($G$4,0))*($B$2:$B$8=$G$3)*($C$2:$E$8="ok"))/SUMPRODUCT(($A$2:$A$8>=(EOMONTH($G$4,-1)+1))*($A$2:$A$8<=EOMONTH($G$4,0))*($B$2:$B$8=$G$3)*($C$2:$E$8<>""))


Where

G3 has the name: John

G4 has any date in the month: 1/1/2013 or 15/1/2013 both for January 2013

 

[rbeccaas]

That is so cool. Any way to pull the range of the month and person's name as to not use separate cells for entry/reference?

Thanks for your help, I enjoy reading your work!

 

[Hui]

Whoops


I forgot to Welcome you to the Chandoo.org Forums, so Welcome!


You can hard code them or use a Named Formula


Hard Code:

=SUMPRODUCT(($A$2:$A$8>=DATE(2013,1,1))*(($A$2:$A$8<=DATE(2013,1,31))*($B$2:$B$8="John")*($C$2:$E$8="ok"))/SUMPRODUCT(($A$2:$A$8>=DATE(2013,1,1))*($A$2:$A$8<=DATE(2013,1,31))*($B$2:$B$8="John")*($C$2:$E$8<>"")))


Named Formula:

=SUMPRODUCT(($A$2:$A$8>=(EOMONTH(Date,-1)+1))*($A$2:$A$8<=EOMONTH(Date,0))*($B$2:$B$8=Name)*($C$2:$E$8="ok"))/SUMPRODUCT(($A$2:$A$8>=(EOMONTH(Date,-1)+1))*($A$2:$A$8<=EOMONTH(Date,0))*($B$2:$B$8=Name)*($C$2:$E$8<>""))


Where the following Named Formula are used:

Name: ="John"

Date: =Date(2013,1,15)


I will always recommend against Hard Coding these values as it removes flexibility and makes maintenance so much harder

 

[rbeccaas]

Thanks for the great welcome with a PERFECT (and quick!) solve.