Larry,
Larry,
You seem to want just (sub)totals per WMS Part. It is not clear to me why you don't use Pivot Tables. What follows might also meet your need.
Consider the following sample in A1:B10 in a sheet called Data:
{"WMS Part#","AVL";
59238,2;
59238,1;
59239,3;
59239,2;
59241,1;
59241,3;
59242,2;
59242,1;
59244,2}
Activate the option Insert|Name|Define.
Enter NumDataRecs in the Names in Workbook box.
Enter in the Refers to box:
=MATCH(9.99999999999999E+307,Data!$B:$B)
Activate Add.
Enter WMSrange in the Names in Workbook box.
Enter in the Refers to box:
=OFFSET(Data!$A$2,0,0,NumDataRecs-1,1)
Activate Add.
Enter WMSrange in the Names in Workbook box.
Enter in the Refers to box:
=OFFSET(Data!$B$2,0,0,NumDataRecs-1,1)
Activate OK.
Note 1. The minus 1 in the NumDataRecs-1 bit refers to the fact that there is one row before the row where the actual data start.
Make a unique list of WMS Part#'s in A from A1 on (including the label 'WMS Part#') in a sheet called Summary. You can create such a list using Advanced Filter. In this sheet:
In B1 enter: SubTotals [ just a label ]
In B2 enter and copy down as far as needed:
=SUMIF(WMSrange,A2,AVLrange)
This is what you get in the results area in the Summary sheet:
{"WMS Part#","SubTotals";
59238,3;
59239,5;
59241,4;
59242,3;
59244,2}
For comparison: A Data|Subtotals approach would give you:
{"WMS Part#","AVL";
59238,2;
59238,1;
"59238 Total",3;
59239,3;
59239,2;
"59239 Total",5;
59241,1;
59241,3;
"59241 Total",4;
59242,2;
59242,1;
"59242 Total",3;
59244,2;
"59244 Total",2;
"Grand Total",17}
Note 2. The range names are so-called dynamic range names, allowing one to add records to and/or delete records from the data in the Data sheet without the need to adjust the SUMIF formula that we use here.
Aladin