HELP! (2) Dependent Combo and (1) Dependent List Box Macro and Sumproduct Buggin out

CAn't figure this one out after four days. Hopefully someone on this forum can help me as I am up against the clock. Although i have the VBA code working for the combo boxes, the sumproduct formulas are not working.

I have to turn off the auto calc to manual since it takes soo long to repopulate this report sheet. When I hit F9 the recalc also appears to run the combo box macros which bugs out my combo boxes. After adding the combo boxes to this Sumproduct driven report the sumproduct formulas stopped working and display #N/A.

The macros were added to the "Report DCC" sheet where the combo boxes they populate are sitting. The second tab is the data tab where the macros are doing there filtering of the Distributor, Category and Collections columns. The Sumproducts are referencing cells B1 thru B4 which the combo boxes are tied to for conditions which it then searches out in the data tab.

Pretty complex report i guess. I attached a copy. Can anyone help me get this working as I am holding on to my seat for help. thx

Thanks Norie. I changed all the ranges that the Sumproduct formulas are dependent on to reflect 79 rows. I also changed them from dynamic ranges which only show up on the sheet where they are named to regular range names so that they can be seen on teh Report DCC sheet where the Sumproduct formulas are.

Unfortunetly, the Sumproduct formulas still do not want to work. I even turned off the manual calc to automatic since I have so few sumproduct formulas as this is an extract from a much larger report.

Anyone can help? Attached is the revised sample file reflecting the changes discussed to the range names above.

Sorry, here is that revised file for the ranges. Can anyone solve this mystery?