Absolute cells in at table

Hello all,
I've been having difficulty using absolute references in a structured (I think that's the correct term ) table.
Essentially Criteria range changes as its dragged across and then reverts back to being correct.
I've attached a very simple example adding a list of units based on a color which I hope demonstrates what I mean.

Richard.

In order to use the columns as absolute, you need to do it like

Formula:

Please Login or Register  to view this content.

Hello and happy new year Ali and Fluff,
Well I've sought of got them both to work, but only by adding a third column to the source table , otherwise i still ended up with 8 for Blue and Green.
But once I'd done this I was able to delete and still get a correct result.
Ive reattached the workbook and left a sheet named test without the third colums showing incorrct results, if I am leaving out vital comma or colon please let me know,
Thanks to both of you.

Richard.

Your formula in I6 is looking is looking at the wrong header (it's counting Blue not green). If I drag the formula from G6 across to I6 it works quite happily.

Hi,
yes, I've tried it again, it this time, I dragged quite slowly and carefully, and you are quite correct this time it has copied across.
thanks,

RD.

Hi Ali,
I've used both replies and both work, but can you explain or point me to where a bit more information about yours, the subtotal
drop down, can found. I know I've come across it somewhere. Once again, thanks, for your help.
I'd give a rating but it won't let me.

Richard.