prevent table columns incrementing in formulas when you drag and fill a range

[merlin777]

I have this:

=COUNTIF(ordertable[Column3],1B) to count all the reds in column 3 of my order table in column 1 of a table that counts the colours in the order table thus:


[TABLE="width: 241"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]colour[/TD]
[TD]red[/TD]
[TD]green[/TD]
[TD]blue[/TD]
[TD]black[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]quantity[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]

There are over 40 columns of colours so i'm trying to drag the formula across the row so the criteria reference will increment but the column reference in the table increments too. Can I stop that from happening?

 

[mole999]

normally it wsould be restrained with a dollar sign $ not sure how that works with tables

 

[merlin777]

normally it wsould be restrained with a dollar sign $ not sure how that works with tables

Found it! No idea how it works though....

ordertable[[Column3]:[Column3]]

and that remains static.