Sorting Countifs

[Andy]

Any help on this would be gratefully accepted. I have a
number of spreadsheets where I have tables with formula
similar to =COUNTIF('Sheet 2'!F:F,'Sheet 1'!A1) in row 1.
However, when I sort on the table of data and row 1’s data
moves to row 20 for example, the formula will still look
for the A1 value instead of altering to look for A20. Is
there anyway that I can allow the criteria to change along
with the sort??? Please help this is annoying me
greatly!!!

 

[Frank Kabel]

Hi
not really a chance if you sort the other sheet. Excel
always reference the cell and not the value.

 

[Andy]

Hi
I realise this but surely it is possible. Whne you have a
table and one of the columns is a lookup eg =VLOOKUP
(a1,Sheet2A:C,3,false) and you sort the table, this
formula will automatically change to look at cell A#, #
being which ever row this data has now moved to. However,
this does not happen in countifif - surely it is possible?

 

[Frank Kabel]

Hi
in this case you hae a unique identifier in your sorted sheet and an
associated value. If this is the case for your COUNTIF data also you
can combine both formulas of course. so something like
=COUNTIF(A:A,VLOOKUP("ID",'sheet2'!$A$1:$B$100,2,0))

BUT this would require to HARDCODE the identifier or to double this ID
on your first sheet