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 number	Job scheduled on	Customer name	Employee name	Job completed on	Job type	Job total
11775	7/19/2013 15:00:00	Abramowitz, Andy	William Lutz		Open Estimate	2614.37
11121	4/29/2013 16:30:00	Jackson, Tahira	William Lutz		Open Estimate	177.07
12058	8/19/2013 14:00:00	Avila, Linda	Daniel, Thum		1-2 Bid and do	0
11188	5/8/2013 17:00:00	Bauer,	Chad Jeffries		Open Estimate	439.77
11452	6/12/2013 10:00:00	Bazemore, Lenny	Luke Short		6-8 Day Job	4958.08
11600	6/27/2013 9:00:00	Bazemore, Lenny	Chad Jeffries		Open Estimate	978.5
11137	5/1/2013 10:00:00	Bazemore, Lenny	Chad Jeffries	5/14/2013 14:26:00	Days (6+)	100
10507	1/24/2013 11:00:00	Benjamin Cobrin & Company Realtors	William Lutz	2/12/2013 16:05:00	2-4 Half Day Job	870
11169	5/6/2013 14:00:00	Bierman, Dr Robert	Chad Jeffries	6/19/2013 9:59:00	1-2 Small job	256.84
10303	1/3/2013 9:00:00	Branham, Cheryl	William Lutz		Open Estimate	1386.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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive