Wednesday, March 18, 2015

yogi_Conditionally Format Cells In Column I If Row By Row Sum Of Cumulative Hours By Date And By Client Is Over 6


                                           Google Spreadsheet   Post  #1926
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2015
question by ari5000:
https://productforums.google.com/forum/#!mydiscussions/docs/S-yp2TqlTvQ
Help creating a Sum Filter Array MMULT formula in Sheets

Above is link to mock up of my spreadsheet.
I would like to build a formula that SUMS up total hours (G) each day (A), for each unique client (D). This has to be an array formula, I think, because new entries are added each day.
I have tried to create a sum filter array formula that uses mmult function but failed. Basically, the formula needs to check for identical dates, then match them with each unique set if identical clients, then once it groups each unique set of dates with each unique set of clients, add up the total hours and see if it's over six.
After hours of research all I came up with was this and it obviously does not work but I think it's sort of on the right track:
=SUM( FILTER( ARRAYFORMULA( MMULT( ($G$2:$G=TRANSPOSE($A2:$A)) * TRANSPOSE($D2:$D) , SIGN(ROW($G2:$G)); ???))))
The goal is not to print any totals, but simply to create an alert if the total is over 6 hours.
-------------------------------------------------------------------------------------------------------------------------


No comments:

Post a Comment