I figured I'd ask since I can't seem to find anything googling this.

So I've got a spread sheet with 2 sheets that contain data and a 3rd sheet that produces a table of information from the data. There is a drop down allowing me to change the tables based on the drop down field. I cannot change the data tables by adding columns. I also do not believe a macro will work here due to how the macros already in the sheet work.

OK so now I want to get a count of items on table 1 that are not on table 2. I also want to add up the values for those items on table 2 but not table 1. Clearly this is easy if I add a column and do the look up on one table to the other and then use that column as an identifier in a sumifs statement. So this means I believe I'll need to use the sumifs and countifs statements and embed a look up that will do this, but I can't seem to think of a way to do this.

Anyone have any ideas? And all things being equal avoiding large arrays would be nice given the processor demands with excel.