Saturday, August 3, 2013

yogi_Extract Maximum Value For Month Column By Column By Name

                                          Google Spreadsheet   Post  #1316
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug, 3 2013
user  Tim Brehaut (http://productforums.google.com/forum/?zx=t4pky2oc4l45#!category-topic/docs/spreadsheets/G6dIXDBPdzk)
Arrayformula/Query not working as desired
I'm trying to summarize data from Sheet2 into Sheet1. Sheet2 is populated from Form submissions and includes names in column B, months in column D, and a score in column T. On Sheet 1 I would like to create a formula to check the name in column A (to the left) and the month in row 3 (above) and return the highest matching score from column T. I've tried both Query and ArrayFormula but can't quite get it to work.

The closest I've come (which works for small amounts of data) is: 

=arrayformula(max((Sheet2!$B$3:$B$50=$A3)*(Sheet2!$D$3:$D$50=E$2)*(Sheet2!$T$3:$T$50)))

however, it doesn't work when I take out any row references:

=arrayformula(max((Sheet2!$B:$B=$A3)*(Sheet2!$D:$D=E$2)*(Sheet2!$T:$T)))

I expect to have thousands of entries and any row number larger than 100 or so doesn't seem to work either.

I also attempted:

=query(Sheet2!$A:$T,"Select max(T) where B ='" & $A6 & "' and D ='" & D$2 & "'",0)

which returns the word "max" with the maximum (desired) value in the cell below, instead of the actual cell the formula is in.

I'm stumped.

Finally, the names in column A are generated with the Unique function. Any idea on how I can have the rest of the row (with the desired formulas above) self-populate when a new unique name is added?

Thanks greatly for any assistance!
----------------------------------------------------------------------------------------------------------------------------------------------------