MAXIF Array using sum of duplicate value and known value

[ericdb]

Hello, I am trying to figure out how to create a MAXIF Array function using duplicated value sums, and a known criteria value.

Example:
I have a shipment report that shows parts shipped down to a part and serial number. I need to summarize the data by part number showing only the largest shipment qty by part number. The report has duplicated lines for order number and part number due to the serial numbers. I need an array function that will sum all of the part numbers (known value) for each order number, and provide the max value. In the Table example, i would need Max for Part1 to be 5 (order one shipped 5) and Part2 to be 4 (order two shipped 4).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Part Number[/TD]
[TD]Serial Number[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12345[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]23456[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]34567[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]45678[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]56789[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]67890[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]78901[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]89012[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]90123[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]01234[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]98765[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]87654[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]76543[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]65432[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]54321[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]43210[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl200, width: 80"]Part 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]32109[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated.

Thanks!
Eric

 

[lrobbo314]

Not sure if there is a simpler way to do this, but this seems to work.

I copied your info into range A1:C18.

In D2 I have this formula, =COUNTIFS($A$2:$A$18,A2,$B$2:$B$18,B2)
In E3 I have this formula, =MAX(IF($A$2:$A$18=A2,$D$2:$D$18))=D2, this is an array formula, Ctrl+Shift+Enter

Copy both of the formulas down.
Then create a pivot table with the new columns.
Rows should have Order Number and Part Number.
Values should have Count of Part Number.
Then add Column E, whatever you named it on your main sheet, to the Filters section. Then go to your pivot table and select TRUE for the filter you just added.

 

[Marcelo Branco]

If column A (OrderNumber) is numeric, not alphanumeric, try this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Order Number​

[/td][td]

Part Number​

[/td][td]

Serial Number​

[/td][td][/td][td]

Part Number​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

Part1​

[/td][td]

12345​

[/td][td][/td][td]

Part1​

[/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

1​

[/td][td]

Part1​

[/td][td]

23456​

[/td][td][/td][td]

Part 2​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

1​

[/td][td]

Part1​

[/td][td]

34567​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

1​

[/td][td]

Part1​

[/td][td]

45678​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

1​

[/td][td]

Part1​

[/td][td]

56789​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

1​

[/td][td]

Part 2​

[/td][td]

67890​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

1​

[/td][td]

Part 2​

[/td][td]

78901​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

1​

[/td][td]

Part 2​

[/td][td]

89012​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

2​

[/td][td]

Part 2​

[/td][td]

90123​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

2​

[/td][td]

Part 2​

[/td][td]

1234​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

2​

[/td][td]

Part 2​

[/td][td]

98765​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

2​

[/td][td]

Part 2​

[/td][td]

87654​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

2​

[/td][td]

Part1​

[/td][td]

76543​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

3​

[/td][td]

Part1​

[/td][td]

65432​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

3​

[/td][td]

Part1​

[/td][td]

54321​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

3​

[/td][td]

Part 2​

[/td][td]

43210​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td]

3​

[/td][td]

Part 2​

[/td][td]

32109​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in F2 copied down
=MAX(FREQUENCY(IF(B$2:B$18=E2,A$2:A$18),A$2:A$18))
Ctrl+Shift+Enter

M.

 

[ericdb]

Hello Marcelo, Thank you for the response. The order number is actually alpha numeric. Sorry for not specifying.

 

[ericdb]

Not sure if there is a simpler way to do this, but this seems to work.

I copied your info into range A1:C18.

In D2 I have this formula, =COUNTIFS($A$2:$A$18,A2,$B$2:$B$18,B2)
In E3 I have this formula, =MAX(IF($A$2:$A$18=A2,$D$2:$D$18))=D2, this is an array formula, Ctrl+Shift+Enter

Copy both of the formulas down.
Then create a pivot table with the new columns.
Rows should have Order Number and Part Number.
Values should have Count of Part Number.
Then add Column E, whatever you named it on your main sheet, to the Filters section. Then go to your pivot table and select TRUE for the filter you just added.

Thanks Irobbo314 for the reply. I am actually trying to add this to a very large table as a field to build more functions off of for stocking levels, so single column results is how I really need this to work.

 

[Marcelo Branco]

Hello Marcelo, Thank you for the response. The order number is actually alpha numeric. Sorry for not specifying.

Try this array formula instead in F2 copied down
=MAX(FREQUENCY(IF(B$2:B$18=E2,MATCH($A$2:$A$18,$A$2:$A$18,0)),ROW($A$2:$A$18)-ROW(A$2)+1))
Ctrl+Shift+Enter

M.

 

[barry houdini]

For. a single formula try this

=MAX(COUNTIFS(B$2:B$18,E2,A$2:A$18,A$2:A$18))

confirm with CTRL+SHIFT+ENTER

 

[ericdb]

Try this array formula instead in F2 copied down
=MAX(FREQUENCY(IF(B$2:B$18=E2,MATCH($A$2:$A$18,$A$2:$A$18,0)),ROW($A$2:$A$18)-ROW(A$2)+1))
Ctrl+Shift+Enter

M.

Awesome Marcelo!! This works perfectly. I tried to do something similar but it did not work, and was trying to merge a SUM with Frequency, with MAX.

Thanks Again!!!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.