yogi_Compute Total Score For Specified Candidate For Latest (or specified week) For An Open-Ended Number Of Weeks

                                          Google Spreadsheet   Post  #1365
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep 09, 2013
user Joy Bird (http://productforums.google.com/forum/?zx=i955ropbiggh#!mydiscussions/docs/86hQ_qA4WGc)
query inside of a sumif/sum(filter
I want to count scores of students for the entire year, but also on a weekly basis.

I need a function that will sum the scores associated with the students' initials, AND that correspond to the max number in row 1. So basically, sum if column A = BJ AND row 1 = the max so far.  

So far, I used the query function to sum the scores in the entire table if column A = BJ.  I used a separate function to count the scores in row 3 if row 1 = the max so far.  But I need to combine the two so that I don't have to count row by row.  

https://docs.google.com/spreadsheet/ccc?key=0AoBn7ck7bJXzdGRuYjR6bkRuSnUtcTdxcTluVmVWWlE&usp=sharing

---

Oops, I changed the sharing, so it should work now.  The equations are on the sheet, but I'll put them here too.

sum(query(Data!A:H, "Select * where A = 'BJ'"))  - This gives me ALL of BJ's scores.

sum(filter(Data!3:3;Data!1:1=(Max(Arrayformula(Data!1:1)))))  This gives me BJ's scores in row 3 that are from the second (most recent) week

I need an equation that gives me BJ's scores from the most recent week.

----------------------------------------------------------------------------------------------------------------------------------------------------------