change column ref when dragging a count if down

Hi
you can change reference column/row/cell/... by INDIRECT function.

Enter the column letter name of each question in column G, then use INDIRECT function as follow:
1.PNG
consider the formula in cell I3
1-

PHP Code: 

=COUNTIF(INDIRECT($G3&"2:"&$G3&13,TRUE),"yes") 


2-

PHP Code: 

=COUNTIF(INDIRECT("$A2:$A13",TRUE),"yes") 


then INDIRECT function returns a valid reference from a given text string.
3-

PHP Code: 

=COUNTIF($A2:$A13,"yes") 


plz check attached file

Please try at I3 drag to J3 and drag down

=COUNTIF(INDEX($A$2:$E$13,,ROWS(I$3:I3)),I$2)

Then Please try at J3 copy to K8

=COUNTIF(INDEX(Sheet1!$A$2:$E$13,,MATCH(REPLACE($I3,2,0,"UESTION "),Sheet1!$A$1:$E$1,)),J$2)

You may change Q1 to other Q# for testing.

Originally Posted by moneypennie21

one question RasoolS13 - how does this work if the source data is on a different tab as I cannot work out how this dictates the source data? I have added another test file to demonstrate what I mean as I have moved the table with the formulas onto sheet 2 but I want to use the source data on sheet 1. Can you help me?

Hello

Just add the name of the worksheet at the beginning of the range, as follows:

PHP Code: 

=COUNTIF(INDIRECT("'Sheet1'!"&$H3&"2:"&$H3&13,TRUE),"yes") 


I'm glad that the answer was helpful to you