## Tuesday, August 13, 2013

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
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,