Tuesday, August 13, 2013

GOOGLE SPREADSHEET CONDITIONAL SUM USING MMULT

Question:


( by Gen 3 Electric )

Help!! I'm hitting a road block with a conditional sum. I have 4 columns:
A) List of Names
B) A List of job completed dates.....Blank if not completed.
C) Job $ amount 
D) Shorter list of unique names
I'm looking for the SUM of COMPLETED jobs for UNIQUE names. I can't get my filter to match the name and only sum if there is a date(not blank) in the competed column. 
Thank you for looking at this and helping me get unstuck.

==============

INFORMATION FROM A REPORT
Job numberJob scheduled onCustomer nameEmployee nameJob completed onJob typeJob total
117757/19/2013 15:00:00Abramowitz, AndyWilliam LutzOpen Estimate2614.37
111214/29/2013 16:30:00Jackson, TahiraWilliam LutzOpen Estimate177.07
120588/19/2013 14:00:00Avila, LindaDaniel, Thum1-2 Bid and do0
111885/8/2013 17:00:00Bauer,Chad JeffriesOpen Estimate439.77
114526/12/2013 10:00:00Bazemore, LennyLuke Short6-8 Day Job4958.08
116006/27/2013 9:00:00Bazemore, LennyChad JeffriesOpen Estimate978.5
111375/1/2013 10:00:00Bazemore, LennyChad Jeffries5/14/2013 14:26:00Days (6+)100
105071/24/2013 11:00:00Benjamin Cobrin & Company RealtorsWilliam Lutz2/12/2013 16:05:002-4 Half Day Job870
111695/6/2013 14:00:00Bierman, Dr RobertChad Jeffries6/19/2013 9:59:001-2 Small job256.84
103031/3/2013 9:00:00Branham, CherylWilliam LutzOpen Estimate1386.02

UNIQUE EMPLOYEES ON SEPARATED SHEET

Employee name
William Lutz     
Daniel, Thum
Chad Jeffries
Luke Short
David Cusson

 EXPECTED RESULTS:
William Lutz               $870
Daniel Thum               $0
Chad Jeffries               $356.84
Luke Short                  $0
Dave Cusson               $0

Solution:


Have a look at the following screenshot of "Sheet1":




Have a look at the following screenshot of "Sheet2":


in the above sheet, I have the following formulas in Cell 
A2:
=unique('Sheet1'!D2:D)

B2:
=arrayformula(if(isblank(A2:A);iferror(1/0);mmult(sign(A2:A=TRANSPOSE(if('Sheet1'!E2:E="";"";'Sheet1'!D2:D)));if('Sheet1'!E2:E="";"";'Sheet1'!G2:G))))


ANOTHER WAY OF SOLUTION:

Have a look at the following screenshot of "Sheet3":



in the above sheet, I have the following formula in Cell A1:
=query('Sheet1'!A:G;"select D,sum(G) where D<>'' and E is not null group by D label sum(G) 'Total' ")



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

No comments:

Post a Comment