get value for matching criteria

I tried both solutions but neither did the trick. I added to the workbook what the resulting values should look like.

Originally Posted by FDibbins

Sounds to me like you want 2 different answers here?

1, Sum by week by Order Number...
=SUMIF($A$2:$A$15,$K2,C$2:C$15)

2. max line item number by order...
=MAX(IF($A$2:$A$15=K2,$B$2:$B$15,0))
This 1 is an ARRAY formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

This is actually close. I need to sum by max line number by Order, by week.

for example for order number 222345, for week 1 it should return a value of 45, as the max line number is 2. for week 2 it should then return 5.

Look at it as though your producing widgets and you only wanted count completed widgets. When multiple line items are shown the largest line item number matched to the corresponding order number would represent the last manufacturing step. So essentially i only want to count completed widgets

I solved it, the following as an ARRAY formula does the trick.

=SUMIFS(C$2:C$15,$A$2:$A$15,$K2,$B$2:$B$15,MAX(IF($A$2:$A$15=$K2,$B$2:$B$15)))

Thanks for your help Ford