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