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.

	A	B	C	D
1	WO	CUSTOMER	STATUS	COST
2	10/18/17	ATLRAL	6	$395.00
3	10/18/17	ATLRAL	4	$222.00
4	10/18/17	BILSYL	4	$159.00
5	10/18/17	DIATEM	6	$3,000.00
6	10/18/17	DIATEM	6	$2,000.00
7	10/18/17	ROBTAM	5	$223.00
8	10/18/17	VALSAL	6	$50.00
9	10/18/17	BILHOU	5	$1,852.00
10	10/18/17	BILHOU	6	$2,310.00
11	10/25/17	DAVSTO	7	$1,400.00
12	10/25/17	DIATEM	4	$2,796.00
13	10/25/17	FURJAM	7	$2,142.00
14	10/25/17	FURJAM	7	$499.00
15	10/25/17	FURJAM	7	$595.00
16	10/25/17	FURJAM	7	$838.00
			SUM OF COMPLETED ORDERS=	$10,524.00

<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>
</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....

Row Labels	CUSTOMER	STATUS	Sum of COST
10/18/2017	ATLRAL	6	395
	BILHOU	6	2310
	DIATEM	6	5000
	VALSAL	6	50
10/25/2017	DAVSTO	7	1400
	FURJAM	7	4074
Grand Total			13229

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>

 

[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.

WO	CUSTOMER	STATUS	COST	CONCATENATE(A2,B2)	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))
10/18/17	ATLRAL	6	$395.00	43026ATLRAL	64.02%
10/18/17	ATLRAL	4	$222.00	43026ATLRAL	64.02%
10/18/17	BILSYL	4	$159.00	43026BILSYL	0.00%
10/18/17	DIATEM	6	$3,000.00	43026DIATEM	100.00%
10/18/17	DIATEM	6	$2,000.00	43026DIATEM	100.00%
10/18/17	ROBTAM	5	$223.00	43026ROBTAM	0.00%
10/18/17	VALSAL	6	$50.00	43026VALSAL	100.00%
10/18/17	BILHOU	5	$1,852.00	43026BILHOU	55.50%
10/18/17	BILHOU	6	$2,310.00	43026BILHOU	55.50%
10/25/17	DAVSTO	7	$1,400.00	43033DAVSTO	100.00%
10/25/17	DIATEM	4	$2,796.00	43033DIATEM	0.00%
10/25/17	FURJAM	7	$2,142.00	43033FURJAM	100.00%
10/25/17	FURJAM	7	$499.00	43033FURJAM	100.00%
10/25/17	FURJAM	7	$595.00	43033FURJAM	100.00%
10/25/17	FURJAM	7	$838.00	43033FURJAM	100.00%
					$10,524.00
					SUMPRODUCT((F2:F16=1)*(D2:D16))

<colgroup><col span="3"><col><col><col></colgroup><tbody>
</tbody>