COUNTIFS Dynamic Column

[tobermory]

Hello there,

I want to use the COUNTIFS with a dynamic columns. Assuming the below is the data set in Sheet1.

	Test 1	Test 2	Test 3	Test 4	Test 5
Person 1	6	2	1	2	4
Person 2	0	1	3	0	7
Person 3	4	2	3	3	3
Person 4	4	3	4	3	4
Person 5	4	3	4	2	5
Person 6	4	2	4	3	4
Person 7	5	3	5	3	4
Person 8	4	2	5	3	6
Person 9	7	3	1	4	3
Person 10	4	4	3	1	3

<tbody>
</tbody>

In Sheet 2, the 'Tests' are listed the other way around, down a column, not across a row. i.e. 'Test 1' in A2, 'Test 2' in A3, 'Test 3' in A4 etc. How do I create a dynamic COUNTIF in Column C based on lookup/index match in column B? i.e. How many 4s were there in Test 1?

	0	1	2	3	4	5	6	7	8	9	10
Test 1	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif
Test 2	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif
Test 3	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif
Test 4	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif
Test 5	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif	countif

<tbody>
</tbody>

I hope that makes sense, many thanks in advance for your answers.

 

[James006]

Hi,

In Sheet 2 cell B2 ... you can test .

=COUNTIF(OFFSET(Sheet1!$B$2:$B$11,0,ROW()-2),COLUMN()-2)

Hope this will help

 

[ExcelElliott]

This would also work:

=COUNTIF(INDEX($B$1:$F$11,,MATCH($J3,$B$1:$F$1,0)),K$2)

 

[ExcelElliott]

Sorry, didn't read the full post. Since you need this on Sheet2:

=COUNTIF(INDEX('Sheet1'!$B$1:$F$11,,MATCH($A2,'Sheet1'!$B$1:$F$1,0)),B$1)

 

[bprime]

This would also work:

=COUNTIF(INDEX($B$1:$F$11,,MATCH($J3,$B$1:$F$1,0)),K$2)

Thanks so much

 

[bprime]

In the spirit of community contribution, if you have another column to look for in addition you'd just add another index stipulation to countif


=iferror(COUNTIFS(INDEX($E$4:$BT,,MATCH(P4,$E$3:$BT3,0)),TRue,INDEX($E$4:$BT,,MATCH($P$3,$E$3:$BT3,0)),TRue),"")