Hi–I think my problem could be solved by a combination of lookup and sumproduct but I cannot figure it out. I have a group of different omelettes and a few of those omelettes roll up to a more general group (i.e., NY, PA, and NC Omelettes roll into East Coast). I need to do a weighted average of NY/PA/NC Omelettes for East Coast. I need the formula to first look into the Level column C. If 1, find the price in the data sheet not shown here. If 2, go to column A and find OMEL in this case, find all the rows that have OMEL (however many rows) in the Code column D, and do a sumproduct with the Category Mix % and Avg Price for those rows and put the weighted average in the cell. If there is a way to make the formula not circular, that would be great. If there are other ways that would be wonderful as well. Thanks so much!
|1||Item #||Omelettes||Level||Code||Category Mix||Avg Price|
|4||OMEL||East Coast Omelettes||2||30.0%||$0.00|