Sumproduct only if multiple criteria is met in multiple rows

[John Luther]

Hi,

I am trying to solve a problem using sumproduct but can't quite figure it out:

I have a table (illustrated below) in which I'm trying to sum the total cost (column D) when the status (column C) is equal to or greater than 6 for any particular customer (column B) on a work order (column A). So for this data set, I do not want a sum that includes row 2 for example, even though it meets all the criteria, because the same customer on the same work order in the next row, has a status of 4. I only want to sum the cost of the "complete" orders, when every row for a customer/work order is status 6 or 7. I can do this with a helper column but I want to avoid that if possible. I hope I've been able to communicate my problem effectively. I'd appreciate any help I can get.

<colgroup><col style="mso-width-source:userset;mso-width-alt:768;width:16pt" width="21"> <col style="width:65pt" width="86" span="3"> <col style="width:65pt" width="86"> </colgroup><tbody>
[TD="class: xl65, width: 21"][/TD]
[TD="class: xl65, width: 86"]A[/TD]
[TD="class: xl65, width: 86"]B[/TD]
[TD="class: xl65, width: 86"]C[/TD]
[TD="class: xl71, width: 86"]D[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl66"]WO
[/TD]
[TD="class: xl66"]CUSTOMER[/TD]
[TD="class: xl66"]STATUS[/TD]
[TD="class: xl70"]COST[/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl67"]10/18/17[/TD]
[TD="class: xl65"]ATLRAL[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl71"]$395.00[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl67"]10/18/17[/TD]
[TD="class: xl65"]ATLRAL[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl71"]$222.00[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl67"]10/18/17[/TD]
[TD="class: xl65"]BILSYL[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl71"]$159.00[/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl68"]10/18/17[/TD]
[TD="class: xl69"]DIATEM[/TD]
[TD="class: xl69"]6[/TD]
[TD="class: xl72"]$3,000.00[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl68"]10/18/17[/TD]
[TD="class: xl69"]DIATEM[/TD]
[TD="class: xl69"]6[/TD]
[TD="class: xl72"]$2,000.00[/TD]

[TD="class: xl65"]7[/TD]
[TD="class: xl67"]10/18/17[/TD]
[TD="class: xl65"]ROBTAM[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl71"]$223.00[/TD]

[TD="class: xl65"]8[/TD]
[TD="class: xl68"]10/18/17[/TD]
[TD="class: xl69"]VALSAL[/TD]
[TD="class: xl69"]6[/TD]
[TD="class: xl72"]$50.00[/TD]

[TD="class: xl65"]9[/TD]
[TD="class: xl67"]10/18/17[/TD]
[TD="class: xl65"]BILHOU[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl71"]$1,852.00[/TD]

[TD="class: xl65"]10[/TD]
[TD="class: xl67"]10/18/17[/TD]
[TD="class: xl65"]BILHOU[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl71"]$2,310.00[/TD]

[TD="class: xl65"]11[/TD]
[TD="class: xl68"]10/25/17[/TD]
[TD="class: xl69"]DAVSTO[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl72"]$1,400.00[/TD]

[TD="class: xl65"]12[/TD]
[TD="class: xl67"]10/25/17[/TD]
[TD="class: xl65"]DIATEM[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl71"]$2,796.00[/TD]

[TD="class: xl65"]13[/TD]
[TD="class: xl68"]10/25/17[/TD]
[TD="class: xl69"]FURJAM[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl72"]$2,142.00[/TD]

[TD="class: xl65"]14[/TD]
[TD="class: xl68"]10/25/17[/TD]
[TD="class: xl69"]FURJAM[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl72"]$499.00[/TD]

[TD="class: xl65"]15[/TD]
[TD="class: xl68"]10/25/17[/TD]
[TD="class: xl69"]FURJAM[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl72"]$595.00[/TD]

[TD="class: xl65"]16[/TD]
[TD="class: xl68"]10/25/17[/TD]
[TD="class: xl69"]FURJAM[/TD]
[TD="class: xl69"]7[/TD]
[TD="class: xl72"]$838.00[/TD]

[TD="class: xl65"][/TD]

[TD="class: xl73"][/TD]

[TD="class: xl73"][/TD]

[TD="class: xl65"][/TD]

[TD="class: xl74"]SUM OF COMPLETED ORDERS=[/TD]
[TD="class: xl71"]$10,524.00[/TD]

</tbody>

Thanks,

John

 

[gabyrado]

Hi John,
What about using Sumif?

Supposing the table starts from cell A1:

=SUMIF(D3:D17;">=6";E3:E17)

Does that work for you?

Best,

 

[ferrigeu]

honestly, I would use a Pivot Table....
[TABLE="width: 361"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]CUSTOMER[/TD]
[TD]STATUS[/TD]
[TD]Sum of COST[/TD]
[/TR]
[TR]
[TD="align: right"]10/18/2017[/TD]
[TD]ATLRAL[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]395[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BILHOU[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2310[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DIATEM[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]VALSAL[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]10/25/2017[/TD]
[TD]DAVSTO[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FURJAM[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4074[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13229[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[ferrigeu]

honestly, I would use a Pivot Table....

 

[John Luther]

I don't want to use a pivot table because I'm working with an existing document with a dashboard that I simply want to add this sum to.

I can use sumproduct to achieve get a sum of all cost for any particular customer on any particular work order, but what I can't figure out how do is get a sum of all customers on a work order that are status 6 or 7.

 

[John Luther]

And I should clarify that I don't want to sum any customer/work order unless all the lines are 6 or 7

 

[John Luther]

I can get the result I want by adding two helper columns, one to concatenate work order and customer, and another that uses that concatenation and sumproduct to get the percentage of the order that is complete. I can then tally all the orders that are 100% complete. I'm still trying to figure out how to get my number more elegantly without the helper columns.

[TABLE="width: 1378"]
<colgroup><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]WO[/TD]
[TD]CUSTOMER[/TD]
[TD]STATUS[/TD]
[TD]COST
[/TD]
[TD]CONCATENATE(A2,B2)
[/TD]
[TD]SUMPRODUCT(($E$2:$E$16=E2)*($C$2:$C$16>5)*($D$2:$D$16))/SUMPRODUCT(($E$2:$E$16=E2)*($D$2:$D$16))
[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]ATLRAL[/TD]
[TD]6[/TD]
[TD]$395.00[/TD]
[TD]43026ATLRAL[/TD]
[TD]64.02%[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]ATLRAL[/TD]
[TD]4[/TD]
[TD]$222.00[/TD]
[TD]43026ATLRAL[/TD]
[TD]64.02%[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]BILSYL[/TD]
[TD]4[/TD]
[TD]$159.00[/TD]
[TD]43026BILSYL
[/TD]
[TD]0.00%
[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]DIATEM[/TD]
[TD]6[/TD]
[TD]$3,000.00[/TD]
[TD]43026DIATEM[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]DIATEM[/TD]
[TD]6[/TD]
[TD]$2,000.00[/TD]
[TD]43026DIATEM[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]ROBTAM[/TD]
[TD]5[/TD]
[TD]$223.00[/TD]
[TD]43026ROBTAM[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]VALSAL[/TD]
[TD]6[/TD]
[TD]$50.00[/TD]
[TD]43026VALSAL[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]BILHOU[/TD]
[TD]5[/TD]
[TD]$1,852.00[/TD]
[TD]43026BILHOU[/TD]
[TD]55.50%[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]BILHOU[/TD]
[TD]6[/TD]
[TD]$2,310.00[/TD]
[TD]43026BILHOU[/TD]
[TD]55.50%
[/TD]
[/TR]
[TR]
[TD]10/25/17[/TD]
[TD]DAVSTO[/TD]
[TD]7[/TD]
[TD]$1,400.00[/TD]
[TD]43033DAVSTO[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]10/25/17[/TD]
[TD]DIATEM[/TD]
[TD]4[/TD]
[TD]$2,796.00[/TD]
[TD]43033DIATEM[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]10/25/17[/TD]
[TD]FURJAM[/TD]
[TD]7[/TD]
[TD]$2,142.00[/TD]
[TD]43033FURJAM[/TD]
[TD]100.00%
[/TD]
[/TR]
[TR]
[TD]10/25/17[/TD]
[TD]FURJAM[/TD]
[TD]7[/TD]
[TD]$499.00[/TD]
[TD]43033FURJAM[/TD]
[TD]100.00%
[/TD]
[/TR]
[TR]
[TD]10/25/17[/TD]
[TD]FURJAM[/TD]
[TD]7[/TD]
[TD]$595.00[/TD]
[TD]43033FURJAM[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]10/25/17[/TD]
[TD]FURJAM[/TD]
[TD]7[/TD]
[TD]$838.00[/TD]
[TD]43033FURJAM[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$10,524.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SUMPRODUCT((F2:F16=1)*(D2:D16))
[/TD]
[/TR]
</tbody>[/TABLE]