Stumped on Combine

Jeffbanner 

I'm not certain what all you are asking for help on, but I hope the following will at least point you in the right direction. It helps that you are dealing with only 2 years of data.

I believe your two years' data are on separate worksheets, but for clarity, in the attached workbook, I put them both – along with the summary calculations – on one worksheet. I will assume that you know how to refer to cells on a different worksheet.  Although I used fixed cell addresses to refer to the annual data, I can recommend that you use named ranges for the different columns and years, in place of those addresses.

COLUMN A

This contains a combined list of Part Numbers. (I listed my technique in the attached workbook. Although I sorted my list, that's not required.)

COLUMN B

This contains the Part Descriptions. This is done by a lookup on the Part Number in one of the two year's data; if the lookup there fails, the formula does a lookup in the other year's data.  Specifically for cell B2:

=XLOOKUP( A2, E$2:E$13, F$2:F$13, XLOOKUP(A2,I$2:I$7,J$2:J$7,"?",0), 0 )

I assume the part descriptions for a Part Number are the same in both years. If they are not, you can easily change which year's description data is checked first.

COLUMN C

It's not clear, but you appear to want the calculation of the average only where both years have sales data for a part. You could do this using helper columns, but this is where the LET function helps.

My formula calculates the total quantities sold for each part by year, and only calculates the average if both years' quantities are greater than zero.  This formula (specifically for cell C2) is split to multiple lines using Alt + Enter, for clarity):

=LET( Yr1Tot, SUMIF(E$2:E$13, A2, G$2:G$13),
    Yr2Tot, SUMIF(I$2:I$7, A2, K$2:K$7),
    IF( AND(Yr1Tot > 0, Yr2Tot > 0), (Yr1Tot+Yr2Tot)/2, "-" )
)

(In my sample data I included just one row per Part Number per year. As your data may have multiple such rows, note that the SUMIF functions will add the quantities from however many rows in the specified range have the specified Part Number.)