Fixing Table Column References

[CPCCGJDE]

I'm trying to map out pathways within my data set. I have 2 stages of what can happen and where so I'm running a countif function that looks at location 1, what happened at location 1, location 2, what happened at location 2.

I have 5 variables on location and 10 on what happens with over 30,000 logs of these pathways. What I'm trying to do is count the number of times each possible pathway occurs so i've set up this table.

the current formula looks a little like this ......

=COUNTIFS(Table1[What Happened Loc 1],A$75,Table1[What Happened Loc 2],B75,Table1[Location 1st],C$74,Table1[Location 2nd],G$74)

As you can see I'm perfectly capable of locking the specific criteria cells but when I copy it to the right I want to be able to lock the table column references rather than them sliding to next columns along.

Any answers would be amazing

 

[RoryA]

Try doubling them up:

=COUNTIFS(Table1[[What Happened Loc 1]:[What Happened Loc 1]],A$75,Table1[[What Happened Loc 2]:[What Happened Loc 2]],B75,Table1[[Location 1st]:[Location 1st]],C$74,Table1[[Location 2nd]:[Location 2nd]],G$74)

also if you select all the cells in the row then f2 and Ctrl+Enter rather than filling across (or copy/paste) I think you avoid the reference changes.

 

[CPCCGJDE]

That's great

The doubling up worked so didn't need to try the other option.

Thanks